----- Original Message -----
From: "Michael Stassen" <Michael.Stassen@stripped>
To: <SGreen@stripped>
Cc: <kevin@stripped>; <mysql@stripped>; "'Rhino'"
<rhino1@stripped>
Sent: Monday, December 05, 2005 10:01 PM
Subject: Re: Select questions
> 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.
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
