Re: Select questions

[Michael Stassen]

Kevin Fricke wrote:
> Hello all....new to the list...having a bit of an issue here.
> 
> I have a reservations table that is linked to three separate tables, food,
> packages and options.  A reservation can have multiple food options,
> packages and options attached to it.
> 
> I am trying to run a query that will pull all of these out in the query
> without all of the duplicate records.  Is this possible?  Can I pull a
> select list into one query result field.
> 
> For example a reservation may look like this:
> 
> Reservation ID: 1
> Client Name: Kevin
> 
> Food
> ----------
> Nachos
> Tacos
> Ice Cream
> 
> Packages
> ----------
> Live Music
> Casino
> 
> Options
> ----------
> Margarita Machine
> Bartender
> 
> Do I have to run three queries to get the food, packages and options?  I was
> hoping that this could be consolidated into one query.
> 
> Thanks for the help!!
> 
> Kevin

My first thought is, why do you want to do it in one query?  Each table has data 
answering a different question, so separate queries make sense.  If you want one 
list as otput, it should be trivial to have your app print the results as one list.

That said, we can get everything in one query by combining the three separate 
queries with a union.  You didn't tell us your table structure, but it would 
look something like:

   (SELECT name AS item FROM Food WHERE reservation_id = 1)
UNION
   (SELECT name AS item FROM Packages WHERE reservation_id = 1)
UNION
   (SELECT name AS item FROM Options WHERE reservation_id = 1);

assuming you've already looked up the client's reservation_id.  You'd get

item
----------
Nachos
Tacos
Ice Cream
Live Music
Casino
Margarita Machine
Bartender

Here's a more complex version where we look up the reservation_id on the fly, 
and label each item with its type:

   (SELECT 'Food   ' AS type, F.name AS item
    FROM Reservations R JOIN Food F ON R.id = F.reservations_id
    WHERE R.client = 'Kevin')
UNION
   (SELECT 'Package' AS type, P.name AS item
    FROM Reservations R JOIN Packages P ON R.id = P.reservations_id
    WHERE R.client = 'Kevin')
UNION
   (SELECT 'Option ' AS type, O.name AS item
    FROM Reservations R JOIN Options O ON R.id = O.reservations_id
    WHERE R.client = 'Kevin');

This should produce

type     item
-------+-------------------
Food     Nachos
Food     Tacos
Food     Ice Cream
Package  Live Music
Package  Casino
Option   Margarita Machine
Option   Bartender

You get the idea:  Your three separate queries can be combined with UNION, so 
long as the columns match up.  See the manual for details 
<http://dev.mysql.com/doc/refman/5.0/en/union.html>.

Michael