Once again, I'm copying the mailing list so that others can contribute to -
and benefit from - the discussion.
It's past my bedtime so I'm going to leave you in the capable hands of
Michael and Shawn :-)
Rhino
----- Original Message -----
From: "Kevin Fricke" <kevin@stripped>
To: "'Rhino'" <rhino1@stripped>
Sent: Monday, December 05, 2005 9:47 PM
Subject: RE: Select questions
> 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:rhino1@stripped]
> Sent: Monday, December 05, 2005 5:19 PM
> To: mysql; kevin@stripped
> 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: <kevin@stripped>
> To: "Rhino" <rhino1@stripped>
> 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" <rhino1@stripped>
>> Subj: Re: Select questions
>> Date: Mon Dec 5, 2005 4:00 pm
>> Size: 2K
>> To: <kevin@stripped>,<mysql@stripped>
>>
>>
>> ----- Original Message -----
>> From: "Kevin Fricke" <kevin@stripped>
>> To: <mysql@stripped>
>> 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
>
>
--
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
