RE: Select questions

[Kevin Fricke]

I am simply trying to build an exportable report of the results.  For
example, I need to run a monthly report that will pull all of the
reservations with food, packages and extras.

I suppose that the only way to do this is to run three separate queries and
then to run a final query using those three results?

Kevin 

-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@stripped] 
Sent: Monday, December 05, 2005 9:01 PM
To: SGreen@stripped
Cc: kevin@stripped; mysql@stripped; 'Rhino'
Subject: Re: Select questions

SGreen@stripped wrote:
> Thank you for the table structures (I prefer the output from SHOW 
> CREATE
> TABLE......) Now, would you mind also posting the actual query you 
> used to produce what you are calling "duplicated" results?
> 
> Thanks!
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine

I would think that would be obvious from his sample output:

   SELECT r.id, r.reservation_date,
          f.food_name,
          p.Product_Name AS package_name,
          e.extra_name
   FROM  Reservations r
   JOIN Food_Details fd ON r.ID = fd.Reservation_ID
   JOIN Food f ON f.ID = fd.Food_ID
   JOIN Product_Details pd ON r.ID = pd.Reservation_ID
   JOIN Products p ON p.ID = pd.Product_ID
   JOIN Extra_Details ed ON r.ID = ed.Reservation_ID
   JOIN Extra_Options e ON e.ID = ed.Extra_ID
   WHERE r.ID = 425;

Now, what is not obvious to me is why you and Rhino think the solution will
be a JOIN.  There are 3 separate lists.  How will a single query join 3
lists without producing a cross product of the 3 lists?  If he's really
determined to do this in a single query, isn't a UNION required, as I
suggested earlier?  Something like

    (SELECT r.id, r.reservation_date,
            'food   ' AS item, f.food_name AS detail
     FROM  Reservations r
     JOIN Food_Details fd ON r.ID = fd.Reservation_ID
     JOIN Food f ON f.ID = fd.Food_ID
     WHERE r.ID = 425)
   UNION
    (SELECT r.id, r.reservation_date,
            'package' AS item, p.Product_Name AS detail
     FROM  Reservations r
     JOIN Product_Details pd ON r.ID = pd.Reservation_ID
     JOIN Products p ON p.ID = pd.Product_ID
     WHERE r.ID = 425)
   UNION
    (SELECT r.id, r.reservation_date,
            'extra  ' AS item, e.extra_name AS detail
     FROM  Reservations r
     JOIN Extra_Details ed ON r.ID = ed.Reservation_ID
     JOIN Extra_Options e ON e.ID = ed.Extra_ID
     WHERE r.ID = 425);

What am I missing?

That said, I don't see any reason to do this in one query.  I think that's
just confusing the sql query with the desired format of the app's output.
After already finding the reservation id and date with a previous query, I
would simply query each list separately:

   SELECT f.Food_Name
   FROM  Reservations r
   JOIN Food_Details fd ON r.ID = fd.Reservation_ID
   JOIN Food f ON f.ID = fd.Food_ID
   WHERE r.ID = 425;

   SELECT p.Product_Name
   FROM  Reservations r
   JOIN Product_Details pd ON r.ID = pd.Reservation_ID
   JOIN Products p ON p.ID = pd.Product_ID
   WHERE r.ID = 425;

   SELECT e.Extra_Name
   FROM  Reservations r
   JOIN Extra_Details ed ON r.ID = ed.Reservation_ID
   JOIN Extra_Options e ON e.ID = ed.Extra_ID
   WHERE r.ID = 425;

It really should be trivial to use the results of those three queries to
produce the desired output from the app.  What is the advantage of a
single-query solution?

Michael