Hi, try this: SELECT courses.coursetitle,reservationid FROM courses,applicant LEFT JOIN reservation ON (reservation.applicantid = applicant.applicantid AND reservation.coursesid = courses.coursesid) WHERE applicant.applicantid = 1
This gives you all courses an reservationid>0 if applicant has signed, NULL if hasn't signed. On Thu, 2002-04-25 at 22:47, tgharris wrote: > 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 > -- Diana Soares Websolut - Soluções Internet Email: [EMAIL PROTECTED] --------------------------------------------------------------------- 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
