This is the way I normally do queries that would be solved with sub-selects in other products:
SELECT F.idFilm, F.title FROM Films F, Loans L WHERE F.idFilm = L.idFilm AND L.dateReturn IS NULL;
Sam A. Funk
At 02:27 +0000 03/01/03, Remi Mikalsen wrote:
Hello,
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)
However, this query is using a subselect, which isn’t supported by MySQL.
-- ---------------------------------------------------------------- |Sam A. Funk http://www.samafunk.com [EMAIL PROTECTED]| |Wichita, KS Guitarist (self-proclaimed) Macintosh Fanatic| |Kansas Bluegrass Association http://www.kansasbluegrass.org/ | ----------------------------------------------------------------
--------------------------------------------------------------------- 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
