MYSQL query to exclude records in specific condition

Question

Hey I have the following MYSQL DB structure for 3 tables with many to many relation. Many users can have many cars and cars can be for many users as showing below:

Users

ID | Name
---------
100|John
101|Smith

Cars

ID | Name
---------
50|BMW
60|Audi

Users_cars

ID | UID | CID
---------
1| 100 |50
2| 100 |60
3| 101 |60

I have a page users_cars.php this page have two drop down lists

1. list of all users
2. list of all cars

In this page you can select a user from user's list and select a car from car's list then click add to insert into users_cars table.

What am trying to do is to exclude from user's drop down list all the users that have been linked with all the available cars from cars table.

In the example above user's drop down list will just have "Smith" because "John" linked with all cars available (BMW,AUDI), if "Smith" also has the BMW he will be excluded from the list. I need a select query for this condition and i don't want to use any nest select query to count user records inside users_cars table

Answer 1

If I understand what you are after you need to use GROUP BY in your query. So to select all users:

SELECT ID, UID FROM Users_cars GROUP BY UID

and for all cars:

SELECT ID, CID FROM Users_cars GROUP BY CID

That will group results that are the same, so you only get one instance of each user, or one instance of each car.

Answer 2

I hope I understood your question right.

I think you can so this using some programming -

With PHP/mysql -

1. Get count of all distinct car ID's
2. Get count of cars for each user. (making sure this lists only unique car ID's)

Loop through all users and in each loop compare the above two and exclude the user where this condition matches.

Answer 3

SELECT *
FROM   users
WEHRE  id NOT IN (SELECT uid
                  FROM   (SELECT          uid, COUNT(cid), COUNT(*)
                          FORM            cars
                          LEFT OUTER JOIN users_cars ON cars.id = users_cars.cid
                          GROUP BY        uid
                          HAVING          COUNT(cid) = COUNT(*)

Answer 4

Basically, what you want to do is that (if I understood your problem) :

SELECT UID  FROM Users_cars WHERE CID NOT IN (SELECT ID FROM Cars);

But carefull, this is a greedy request (depends on the size of the tables of course) and you should better put a flag on the user table and update then when your user uses the last available car (or with a batch) so you don't run the request too often !