Works like a dream! thanks so much. thomas On 26 Apr 2002, Diana Soares wrote:
> 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 > > --------------------------------------------------------------------- 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
