Jump to content


Photo

Simple SQL query problem, can anyone help?


  • Please log in to reply
2 replies to this topic

#1 qwave

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!
Grem

#2 fenway

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

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