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.
What alternatives do I have? Knowing that it is impossible to create Views and
that neither intersect nor minus (as in Oracle), are implemented, I believe the only
option I am left with is using TEMPORARY TABLES. I tried to solve the
problem using precisely a Temporary Table which contained the result from the
subselect above. It didn’t work! The message I got was that MySQL couldn’t find
the temporary table with the specified name!
As one of the major goals with Database Management Systems is program-data
independence I do not want to use a program-dependent solution. Additionally, I
do not want to insert redundancy into the database through an extra attribute in
the films table (tinyint marking 1 if a film is available, 0 if not available).
What could I do to solve this simple problem?
--
Remi André Mikalsen
Homepage - http://mikalsen.no.sapo.pt
--
Remi André Mikalsen
Homepage - http://mikalsen.no.sapo.pt
Email - [EMAIL PROTECTED]
---------------------------------------------------------------------
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
