Re: A Simple Query!

[Bruce Feist]

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