Re: Select questions

[Michael Stassen <suppressed>]

suppressed 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed