How do I query every person that does not know anyone that takes classes?

I’m struggling with the following SQL problem: I must query all persons that do not know anyone who takes classes, using the following tables:

╔═════════╦════════╦═══════════╦═════╗
║ Persons ║        ║           ║     ║
╠═════════╬════════╬═══════════╬═════╣
║ id      ║ name   ║ address   ║ age ║
║ 1       ║ Teddy  ║ Utrecht   ║ 96  ║
║ 2       ║ Harun  ║ Texas     ║ 64  ║
║ 3       ║ Egbert ║ Rome      ║ 68  ║
║ 4       ║ John   ║ Amsterdam ║ 39  ║
╚═════════╩════════╩═══════════╩═════╝
╔═══════╦════════════╦════════════╗
║ Knows ║            ║            ║
╠═══════╬════════════╬════════════╣
║ id    ║ personA_id ║ personB_id ║
║ 25    ║ 1          ║ 2          ║
║ 26    ║ 1          ║ 3          ║
║ 27    ║ 3          ║ 2          ║
║ 28    ║ 3          ║ 4          ║
╚═══════╩════════════╩════════════╝
╔══════════════╦═══════════╦══════════╗
║ TakesClasses ║           ║          ║
╠══════════════╬═══════════╬══════════╣
║ id           ║ person_id ║ class_id ║
║ 35           ║ 1         ║ 50       ║
║ 36           ║ 1         ║ 51       ║
║ 37           ║ 1         ║ 52       ║
║ 38           ║ 1         ║ 53       ║
║ 39           ║ 2         ║ 54       ║
║ 40           ║ 2         ║ 55       ║
║ 41           ║ 2         ║ 56       ║
╚══════════════╩═══════════╩══════════╝

After a long time trying different queries, the following query is the closest I got to the desired result:

select distinct name 
from Persons P
where P.id NOT IN 
(select person_id 
 from TakesClasses T join Knows K 
 on T.person_id = K.personA_id
 where class_id IS NOT NULL)

I seem to get more results back than is necessary for this query (Harun, Egbert and John), but I cannot find the mistake I’m making. Can someone please help me out?

Answer

use this:

select P.id, name 
from Persons P
where P.id NOT IN 
(select K.personA_id
from TakeClasses T join Knows K 
on T.person_id = K.personB_id
)

Advertisement

Answer