Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts

    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.

  • #2
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    seems an awful lot like this

  • #3
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Quote Originally Posted by shyam View Post
    seems an awful lot like this
    I'm getting an Error 403 message.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You do a LEFT join and then check the right-side table for NULL-- those are the missing rows. This thread has a code example.

  • #5
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    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?

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    This article should help, any questions on it drop me a line.

  • #7
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    Very good overview, that helps a lot. Thanks guelph!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •