Re: Select questions

[Rhino]

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