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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
