Hi -- I have been trying to solve this problem with a left join, and wonder what I am missing:
I have three tables: - applicant (applicantid firstname lastname etc) - courses(coursesid coursetitle etc) - reservation (reservationid, applicantid,coursesid etc) What I want to do is get a list of the courses an applicant has signed up for AND the list of courses he hasn't (from the reservation table). so far I thought a left join would work; however since there is more than one applicant in the reservation table, using NULL and NOT NULL don't work, neither does WHERE reservation.applicantid= '1' (with the '1' to be changed to the the applicant's id number) this is as close as I have gotten(using two queries- the first query works: first query: SELECT courses.coursetitle, courses.coursesid from courses LEFT JOIN reservation ON courses.coursesid=reservation.coursesid where reservation.applicantid = 1 order by coursesid +--------------+-----------+ | coursetitle | coursesid | +--------------+-----------+ | dreamweaver1 | 3 | | coursename1 | 13 | | cname12 | 14 | +--------------+-----------+ but I have not suceeded in getting the courses the applicant has NOT signed up for... thes query doesn't work, since it gives courses other applicants have signed up for that are the same as applicant 1...: second query: select courses.coursesid, courses.coursetitle from courses LEFT JOIN reservation ON courses.coursesid=reservation.coursesid where reservation.applicantid != 1 group by coursesidorder by coursesid I looked into temp tables and select as well, but didn't get any closer. It seems one query should solve this. Hopefully someone with more experience can help. thanks, thomas (sql query) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
