Need to select all PersonID's who are not in a table

There are four tables being used in my problem.


"Person" table, primary key is PersonID, with each person's name, email, etc.
"Chapter" table, primary key is ChapterID, with info about each of our chapters (one is selected when using our site).
"Student" table with a PersonID column that associates the row with info from the Person table and a ChapterID column to tie the row to the Chapter table.
And "smallgroup_students" table with a person_id column that ties to the Person table.


So what I'm trying to do is find any student who is not in a small group. Sounds easy...but I'm stumped. I'm finding that the guy who built this DB was not the smartest cookie, but I've got to deal with it.

So here's the logic I need put into a query:

SELECT every student FROM the tables listed above
WHERE the Student.PersonID equals the Person.PersonID
AND the Student.ChapterID equals the currently selected ChapterID
AND the Student.PersonID is not in smallgroup_students.person_id

Hope this makes sense. Let me know if not.

seems an awful lot like this (http://www.terminally-incoherent.com/blog/2005/08/19/minus-query-in-mysql/)

seems an awful lot like this (http://www.terminally-incoherent.com/blog/2005/08/19/minus-query-in-mysql/)

I'm getting an Error 403 message.

You do a LEFT join and then check the right-side table for NULL-- those are the missing rows. This thread (http://codingforums.com/showthread.php?t=108642) has a code example.

Wow, good call. Thanks so much, Fumi. Sorry, I should have searched deeper before posting. Didn't see that one, but that did it. Have a quick link with a really good tutorial you've found that might help me better understand LEFT JOIN's?

This article (http://guelphdad.wefixtech.co.uk/sqlhelp/joins_basic.shtml) should help, any questions on it drop me a line.

Very good overview, that helps a lot. Thanks guelph! :thumbsup: