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
