Q:

SQL NOT IN (Do not know anyone who smokes)

So I have these three tables:
Persons {id, name}
Knows {A_id, B_id}  - (Person A knows Person B)
Smoking {id} - (id -> Persons{id})

Persons:
{1, "Tim"}
{2, "Kim"}
{3, "Jim"}
{4, "Rim"}

Knows:
{1, 2}
{1, 3}
{3, 2}
{3, 4}

Smoking:
{3}

And I need {3, "Jim"} to be returned since he doesn't know anyone who smokes ({1, "Tim"} knows Jim who smokes so he's out)
I tried this query:
SELECT P.name 
FROM Persons P, Knows K
WHERE K.A_id = P.id AND K.B_id NOT IN (SELECT id FROM Smokes)

but it still return "Tim" even he knows 2 people and only 1 of them is smoking. And I need only the persons who's EVERY 'friend' doesn't smoke.. Help!

A:

SELECT p.name 
FROM Persons p
WHERE NOT EXISTS (                -- there does not exist
   SELECT * FROM Knows k          -- a person I know
   JOIN smokes s ON s.id = k.b_id -- who smokes
   WHERE k.A_id = p.id
   );

