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
To: [EMAIL PROTECTED]
Subject: Re: A Simple Query!
Hello, Remi;
Try this; I *think* it'll work (but I'm also new to MySQL):
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;
This provides an interesting example of the difference between "on" and
"where", by the way; it looks self-contradictory, but shouldn't be.
Because of the "left" join, the "on" should pick up Films without
matching Loans, and set the Loans.idFilm to NULL to show that it did.
Bruce Feist
Remi Mikalsen wrote:
>I believe, and hope, I have a very simple question, but as I am
relatively new to
>MySQL I might be missing out on the obvious solution.
>
>Is it possible to use a single MySQL query (with the simple scheme
presented) to
>ask the following question?
>
>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
