SQL NOT查询返回错误的结果

Question

I have 2 SQL tables. users contains users and registrants contains all the registrations done for an event. Given that I have the id of the event - I am successfully printing out those users that are registered for a specific event. However I want to print all the users that are NOT registered for the event and here I fail at producing the correct result. My query is as follows:

SELECT users.fName, users.lName, users.uid
FROM users, registrants
WHERE registrants.tourId = '$id' AND NOT registrants.pId = users.uid

The result of this is an array of 33 users printed out, although I have only 12, 3 of which are already registered for the event. Is the SELECT command wrong or I will need to dig into the php code, although I am pretty sure all the work there is correct.

Answer 1

By specifying no JOIN syntax, but instead specifying a kind of inequality join in the WHERE clause, you are effectively asking SQL to find all combinations of users and registrants, with the additional filter criteria in the WHERE clause, hence the large number of rows.

I believe the query you are looking for is more like this:

SELECT users.fName, users.lName, users.uid
FROM users
WHERE users.uid NOT IN (
   SELECT registrants.pId 
   FROM registrants
   WHERE registrants.tourId = '$id');

"Find all users, except those registered for tourId = '$id'"