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