Re: Select questions

["Rhino" <suppressed>]

----- Original Message ----- 
From: "Michael Stassen" <suppressed>
To: <suppressed>
Cc: <suppressed>; <suppressed>; "'Rhino'" 
<suppressed>
Sent: Monday, December 05, 2005 10:01 PM
Subject: Re: Select questions


> 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.

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/suppressed