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:[EMAIL PROTECTED] Sent: Monday, December 05, 2005 9:01 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com; 'Rhino' Subject: Re: Select questions [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
