----- Original Message -----
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>; "'Rhino'"
<[EMAIL PROTECTED]>
Sent: Monday, December 05, 2005 10:01 PM
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.
That's a fair question. I'm really not certain that the original poster
needs a join. That is simply the initial impression I formed from the
wording of his question, which I think we can agree was somewhat vague. He
wanted to get information from three differently-organized tables into a
single result set: that feels like a join to me.
It also sounded like he had never considered the possibility of a join,
perhaps because he was a newbie who'd never heard of the concept of a join
before. That got me into explaining the concepts and looking for tutorials
that covered joins. As his first reply to the thread showed though, he was
already familiar with joins and I'd misunderstood where he was coming from.
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?
What you're saying all seems quite reasonable but I really can't judge yet
since I'm still not very clear on what he is really trying to accomplish.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.12/192 - Release Date: 05/12/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]