Hi Bruce, yep, u r right.
Anyway, MySQL query optimizer will take care of it ;) regds, -----Original Message----- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 04:00 To: [EMAIL PROTECTED] Subject: Re: A Simple Query! Hello, Uttam; We can simplify this, actually... any time L.idFilm is NULL (i.e., no row in Loans is found), L.dateReturn will have to be NULL as well. So, specifying the L.idFilm IS NULL condition is redundant. It's sufficient to look only at L.dateReturn IS NULL in the WHERE clause. Yours is an improvement over my original, though, in that it's correct <g>. Mine would have missed films having Loans with non-NULL dateReturns. Uttam wrote: >here's my version of the answer: > >SELECT > F.idFilm, F.title >FROM > Films F LEFT JOIN Loans L ON F.idFilm = L.idFilm >WHERE > (L.idFilm IS NULL) OR (L.dateReutrn IS NULL) >; > >regds, > >-----Original Message----- >From: Bruce Feist [mailto:[EMAIL PROTECTED] >Sent: Saturday, March 01, 2003 08:13 > >select F.idFilm, F.title >from Films F left join Loans L on F.idFilm = L.idFilm and L.dateReturn >IS NULL >where L.idFilm IS NULL; > > >Remi Mikalsen wrote: > > >>Scheme: >> Films (idFilm, title) >> Loans (idFilm, dateLoan, dateReturn, idContact, idLoan) >> Contacts (idContact, name) >> >>Question: >> What films aren't on loan right now? >> >>Using the SQL standards it should be possible to write the following >> >> >query. > > >>SELECT F.idFilm, F.title >> >> >>FROM Films F > > >>WHERE F.idFilm NOT IN ( SELECT L.idFilm >> FROM Loans L >> WHERE L.dateReturn IS NULL) >> --------------------------------------------------------------------- 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
