Re: Select questions In-reply-to: <[e-mail]>

[Michael Stassen]

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