[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Select questions

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" <suppressed> 
To: "'Rhino'" <suppressed>
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:suppressed
Sent: Monday, December 05, 2005 5:19 PM
To: mysql; suppressed
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: <suppressed>
To: "Rhino" <suppressed>
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" <suppressed>
Subj:  Re: Select questions
Date:  Mon Dec 5, 2005 4:00 pm
Size:  2K
To:  <suppressed>,<suppressed>
----- Original Message ----- From: "Kevin Fricke" <suppressed> 
To: <suppressed>
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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed

Mail converted by mhonarc 2.6.15
This archive provided courtesy of JSW4.NET, Internet Hosting Services for Small Business.