Forgot to include the query.
select r.id, r.reservation_date, f.name as food_name, p.name as
package_name, e.name as extra_name
from reservations r
left join reservation_food_details fd on r.id = fd.reservation_id
left join food f on fd.food_id = f.id
left join reservation_package_details pd on r.id = pd.reservation_id
left join packages p on pd.package_id = p.id
left join reservation_extra_details ed on r.id = ed.reservation_id
left join extra_options e on ed.extra_id = e.id
order by id desc
-----Original Message-----
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Monday, December 05, 2005 5:19 PM
To: mysql; [EMAIL PROTECTED]
Subject: Re: Select questions
I'm copying the list on this reply so that everyone can benefit from the
discussion....
Thanks for clarifying that you understand joining. The way your question
was
worded, I thought perhaps you were a newbie who had never heard the
concept
before; my apologies for misunderstanding.
The most common cause of duplicate rows in queries is that you have
omitted
one or more join conditions. However, before we can be sure that this is
the
cause of your particular problem, I'd like to get a few pieces of
information from you:
1. What version of MySQL are you using, e.g. 4.0.15? 5.0.16?
2. What are the definitions of the three tables that you are using in your
queries? I'm especially interested in the primary keys of those tables.
3. What are the queries that are returning the duplicate rows?
4. If possible, could you show us a few sample rows of each table? It
really
helps me visualize the data better. Please don't include hundreds,
thousands
or millions of rows! Just a handful of typical rows for each table should
be
plenty.
5. What expected result did you want for the query that is giving you
trouble?
6. What is the actual result that you are getting?
You just haven't given enough information in your question so far for me
to
diagnose your problem with any certainty or give you a solution. If you
answer my questions, anyone here with a bit of SQL background should be
able
to help you.
Rhino
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Sent: Monday, December 05, 2005 5:58 PM
Subject: Re: Select questions
I understand joins. However, when I join the three tables, it will
contain
duplicate reservation id's. If a reservation has three food options, then
the reservation will be duplicated in the result set three times.
Kevin
-----Original Message-----
From: "Rhino" <[EMAIL PROTECTED]>
Subj: Re: Select questions
Date: Mon Dec 5, 2005 4:00 pm
Size: 2K
To: <[EMAIL PROTECTED]>,<mysql@lists.mysql.com>
----- Original Message -----
From: "Kevin Fricke" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, December 05, 2005 3:14 PM
Subject: Select questions
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!!
You should be able to get the data you want in a single result set by
using
an SQL technique called "joining", assuming the tables have columns in
common.
The syntax for doing a join is explained in the MySQL manual for your
particular version of MySQL. The manuals for each version can be found at
this link: http://dev.mysql.com/doc/
However, the manuals don't do a great job of explaining the concept of
the
join.
I just Googled on SQL Tutorials to see if I could find a decent tutorial
that would show joining. Sadly, I did not find a really good tutorial
that
showed all of the join types and also included a three table join.
However,
this one - http://www.w3schools.com/sql/sql_join.asp - does a pretty
decent
job of showing some of the main two table joins. It's also nice and
short.
I
suggest you read this page as carefully as you can and see if you can get
the concept of joining from it. You may even want to create the two tiny
tables they use for their examples and try the actual joins with those
tables.
As you will see, this short tutorial shows you inner joins, left joins
and
right joins, all of which are supported in MySQL. However, it doesn't
show
you a few other join types which are supported in MySQL, like the
self-join.
Unfortunately, I didn't see a tutorial that showed all of the join types
supported by MySQL. Perhaps someone else can suggest a tutorial like
that.
The good news is that some of the more obscure join types like self-joins
aren't used a lot. (They can be very handy in some situations but you
won't
come across those situations too often.)
A three table join is really not much harder than a two table join; the
concept remains the same. The exact syntax depends on which join type you
use. The syntax for a three table inner join (i.e. Table A is
inner-joined
to Table B and the result of that join is inner-joined to Table C)
follows
this example:
---
--- message truncated ---
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.12/192 - Release Date:
05/12/2005
--
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
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.12/192 - Release Date:
05/12/2005
