List:General Discussion« Previous MessageNext Message »
From:Remi Mikalsen Date:March 1 2003 2:27am
Subject:A Simple Query!
View as plain text  

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?

   Films (idFilm, title)
   Loans (idFilm, dateLoan, dateReturn, idContact, idLoan)
   Contacts (idContact, name)

   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
				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  -

Remi André Mikalsen
Homepage  -
Email          -  RemiMikalsen@stripped

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