Better MySQL query?

Question

Here's one example, I have a Car, User, Member, and Dealer tables. At the moment I'm trying to get the name of a dealer who owns a car by matching their userids up

Select userid, name FROM `User` WHERE userid IN 
    (SELECT userid FROM 'Car' WHERE userid in 
        (SELECT userid FROM `Member` WHERE userid IN 
             (SELECT userid FROM `Dealer`)))

This does what I want but I can't help feel there's a better way of doing it? Currently the userid in Car is a FK of the userid in Dealer which is a FK of the userid in Member which is a FK of the userid in User which stores the name.

Can I go straight to getting all the userid's and names of dealers who's id is in the Car table, whilst making sure they're actually a Dealer?

Rhythem Aggarwal · Accepted Answer · 2014-12-05 06:42:47Z

Basically your schema is a downstream schema

Users -> Members -> Dealer -> Car

Good thing is you made all the possible keys that you need here.

So to selct anything in any table just go down stream from Users for example for the data you want

    Select * from `USER` u     
    where
    dealer.user_id = car.user_id and 
    member.user_id = dealer.user_id and 
    u.user_id = member.user_id

The reason i went upstream in matching records is because we want to make as few matching operations as possible. As you can see user table is supposed to contain the maximum records. So i match with car table and get all the user_id where there is a match in dealer. similarly i go from dealer to member and then to user. this means all the records of users will be matched with a lot fewer records that they would have been if i went from users to members to dealer to car.

But this is not fool proof solution. it will depend on your data. because it may be a case where one user may have multiple cars, then it would be better to go downstream.

Saharsh Shah · Accepted Answer · 2014-12-06 06:51:04Z

1

Use JOIN instead of subqueries to fetch the data.

Try this:

SELECT U.userid, U.NAME 
FROM `User` U 
INNER JOIN Car C ON U.userid = C.userid 
INNER JOIN Member M ON C.userid = M.userid 
INNER JOIN Dealer D ON M.userid = D.userid;

edited Dec 6, 2014 at 6:51

answered Dec 5, 2014 at 6:26

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Collectives™ on Stack Overflow

Better MySQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related