List:General Discussion« Previous MessageNext Message »
From:Remi Mikalsen Date:March 1 2003 2:27am
Subject:A Simple Query!
View as plain text  
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          -  RemiMikalsen@stripped

Thread
A Simple Query!Remi Mikalsen1 Mar
  • Re: A Simple Query!Bruce Feist1 Mar
    • RE: A Simple Query!Uttam1 Mar
      • Re: A Simple Query!Bruce Feist1 Mar
        • RE: A Simple Query!Uttam3 Mar
  • Re: A Simple Query!Sam A. Funk1 Mar