Jump to content


Simple SQL query problem, can anyone help?

  • Please log in to reply
2 replies to this topic

#1 qwave

  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 03 June 2006 - 10:17 PM

My web form contains a list of valid subjects that user can 'enroll' in.

The database contains a table of subjects, and also a table of subjects that users have enrolled in.

I need a query that returns a list of subjects that the user has not enrolled in.

Subject table: subject_id, name
Subject_Enrolled table: enrollment_id, subject_id, user_id

So I have to return all names in the subject table unless the particular user already has an entry for the
subject in the subject_enrolled table. What is the best way to get the query that returns the names of
all the subjects that the user has not yet enrolled in?

Thanks in advance!

#2 fenway

  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 June 2006 - 10:48 PM

A LEFT JOIN would be most appropriate (UNTESTED):

SELECT s.name 
FROM Subject AS s
LEFT JOIN Subject_Enrolled AS se USING ( subject_id )
WHERE user_id = '<your user id>' AND se.subject_id IS NULL

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 qwave

  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 03 June 2006 - 11:03 PM

Brilliant! That seems to have done the trick perfectly. Thanks a bunch!

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users