CODEWITHSUNDEEP
sql
DD.
DD.

Reputation: 22001

SQL query help - finding rows with no relationships

Consider a DB with a Client table and a Book table:

Client: person_id

Book: book_id

Client_Books: person_id,book_id

How would you find all the Person ids which have no books? (without doing an outer join and looking for nulls)

Upvotes: 0

Views: 75

Answers (4)

Neil N
Neil N

Reputation: 25268

select *  
from Client as c 
where (select coun(*) from Client_Books where person_id =c.person_id ) = 0

COUNT for completeness, since there are already EXISTS and IN solutions posted.

Upvotes: 0

Sir Graystar
Sir Graystar

Reputation: 703

SELECT * FROM Client WHERE person_id not in (SELECT person_id FROM Client_Books)

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

select * 
from Client  as c
where not exists(select * from Client_Books where person_id =c.person_id )

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

select *
from Client 
where person_id not in (select person_id from Client_Books)

Upvotes: 2

Related Questions