CODEWITHSUNDEEP
sqlms-access
Nyprez
Nyprez

Reputation: 173

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.

Upvotes: 1

Views: 89

Answers (4)

varun kumar dutta
varun kumar dutta

Reputation: 202

Below SQL may help you out too:

SELECT  *
FROM    Car_Owner
WHERE   Rental = False
        AND id NOT IN ( SELECT  id
                        FROM    Car_Owner
                        WHERE   Rental = True )

OR

SELECT  *
FROM    Car_Owner a
WHERE   Rental = False
        AND NOT EXISTS ( SELECT id
                         FROM   Car_Owner b
                         WHERE  b.Rental = True
                                AND b.id = a.id )

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

select * from car_owner
where name not in (select name from car_owner where rental = True);

Upvotes: 1

user5684647
user5684647

Reputation: 

Select * from Car_Owner 
 Where Rental = False
   and Id Not In
      (Select Id from Car_Owner Where Rental = True)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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

Upvotes: 4

Related Questions