Nyprez Nyprez - 5 months ago 8
SQL Question

SQL - find records where id doesn't exist in / have ANY

SQL - find records where a owner dont have ANY rental car.
I've got the following (in MS Access):

Car_Owner
+----+------+---------+--------+
| id | name | Car reg | Rental |
+----+------+---------+--------+
| 1 | Adam | ABC123 | True |
+----+------+---------+--------+
| 2 | John | XXX111 | True |
+----+------+---------+--------+
| 2 | John | XXX222 | False | //Same person as above
+----+------+---------+--------+
| 3 | Eva | YYY999 | False |
+----+------+---------+--------+


How do I find out who dosn't rent ANY car? In this case would be just Eva. John should not be listed since he already got a rental car.

Any help would be much appreciated.

Answer

One method uses aggregation:

select name
from car_owner
group by name
having sum(iif(rental, 1, 0)) = 0;

You can also check that the value is always false by doing:

having max(rental) = 0 and min(rental) = 0