RE: Select questions

[Kevin Fricke]

Rhino:

Thanks for the help.  Here is the story.  I won't include all of the
information as it doesn't really seem pertinent.  I'm using 5.0 for this
application.

Reservations
-----------------------------------
ID(key) | Reservation_Date

Food
-----------------------------------
ID(key) | Food_Name

Food_Details
-----------------------------------
ID(key) | Reservation_ID | Food_ID

Products
-----------------------------------
ID(key) | Product_Name

Product_Details
-----------------------------------
ID(key) | Reservation_ID | Product_ID

Extra_Options
-----------------------------------
ID(key) | Extra_Name

Extra_Details
-----------------------------------
ID(key) | Reservation_ID | Extra_ID

When I try to join them all, I will get multiple records.  Here is an
example:

id	reservation_date	food_name	package_name	extra_name
425	1/27/2006 0:00	Fajitas	Beth Williams	Chips and Salsa
425	1/27/2006 0:00	Sunshine Tray	Caricaturist	Chips and Salsa
425	1/27/2006 0:00	Traditional Sandwich Tray	Casino (50 - 75
people)	Chips and Salsa
425	1/27/2006 0:00	Fajitas	Beth Williams	Server
425	1/27/2006 0:00	Sunshine Tray	Caricaturist	Server
425	1/27/2006 0:00	Traditional Sandwich Tray	Casino (50 - 75
people)	Server
425	1/27/2006 0:00	Fajitas	Beth Williams	Margarita Machines
425	1/27/2006 0:00	Sunshine Tray	Caricaturist	Margarita Machines
425	1/27/2006 0:00	Traditional Sandwich Tray	Casino (50 - 75
people)	Margarita Machines
425	1/27/2006 0:00	Sunshine Tray	Casino (50 - 75 people)	Chips and
Salsa
425	1/27/2006 0:00	Traditional Sandwich Tray	Danny Santos
Chips and Salsa
425	1/27/2006 0:00	Sunshine Tray	Casino (50 - 75 people)	Server
425	1/27/2006 0:00	Traditional Sandwich Tray	Danny Santos
Server
425	1/27/2006 0:00	Sunshine Tray	Casino (50 - 75 people)	Margarita
Machines
425	1/27/2006 0:00	Traditional Sandwich Tray	Danny Santos
Margarita Machines
425	1/27/2006 0:00	Sunshine Tray	Danny Santos	Chips and Salsa
425	1/27/2006 0:00	Traditional Sandwich Tray	Beth Williams
Chips and Salsa
425	1/27/2006 0:00	Sunshine Tray	Danny Santos	Server
425	1/27/2006 0:00	Traditional Sandwich Tray	Beth Williams
Server
425	1/27/2006 0:00	Sunshine Tray	Danny Santos	Margarita Machines
425	1/27/2006 0:00	Traditional Sandwich Tray	Beth Williams
Margarita Machines
425	1/27/2006 0:00	Sunshine Tray	Beth Williams	Chips and Salsa
425	1/27/2006 0:00	Continental Tray	Caricaturist	Chips and
Salsa
425	1/27/2006 0:00	Sunshine Tray	Beth Williams	Server
425	1/27/2006 0:00	Continental Tray	Caricaturist	Server
425	1/27/2006 0:00	Sunshine Tray	Beth Williams	Margarita Machines
425	1/27/2006 0:00	Continental Tray	Caricaturist	Margarita
Machines
425	1/27/2006 0:00	Fajitas	Caricaturist	Chips and Salsa
425	1/27/2006 0:00	Continental Tray	Casino (50 - 75 people)
Chips and Salsa
425	1/27/2006 0:00	Fajitas	Caricaturist	Server
425	1/27/2006 0:00	Continental Tray	Casino (50 - 75 people)
Server
425	1/27/2006 0:00	Fajitas	Caricaturist	Margarita Machines
425	1/27/2006 0:00	Continental Tray	Casino (50 - 75 people)
Margarita Machines
425	1/27/2006 0:00	Fajitas	Casino (50 - 75 people)	Chips and Salsa
425	1/27/2006 0:00	Continental Tray	Danny Santos	Chips and
Salsa
425	1/27/2006 0:00	Fajitas	Casino (50 - 75 people)	Server
425	1/27/2006 0:00	Continental Tray	Danny Santos	Server
425	1/27/2006 0:00	Fajitas	Casino (50 - 75 people)	Margarita Machines
425	1/27/2006 0:00	Continental Tray	Danny Santos	Margarita
Machines
425	1/27/2006 0:00	Fajitas	Danny Santos	Chips and Salsa
425	1/27/2006 0:00	Continental Tray	Beth Williams	Chips and
Salsa
425	1/27/2006 0:00	Traditional Sandwich Tray	Caricaturist
Chips and Salsa
425	1/27/2006 0:00	Fajitas	Danny Santos	Server
425	1/27/2006 0:00	Continental Tray	Beth Williams	Server
425	1/27/2006 0:00	Traditional Sandwich Tray	Caricaturist
Server
425	1/27/2006 0:00	Fajitas	Danny Santos	Margarita Machines
425	1/27/2006 0:00	Continental Tray	Beth Williams	Margarita
Machines
425	1/27/2006 0:00	Traditional Sandwich Tray	Caricaturist
Margarita Machines

-----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