----- 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:
---
Select a.col4, a.col2, a.col3, b.col6, c.col1, c.col9
from Table_A a join Table_B b on a.col1 = b.col2
join Table_C c on c.col5 = a.col1
---
As you can see, you need to know what each table has in common with the
other table(s) participating in the join and then include those conditions
in the ON subclause of the FROM clause.
Hmm, I've just found another short tutorial -
http://www.techbookreport.com/sql-tut3.html - that actually shows a three
table inner join. You might want to have a look at this one after you look
at the first tutorial I suggested.
I wouldn't be surprised if some of what I've just told you is not very clear
to you because you may lack the concepts to follow the rather rushed and
superficial explanations. Unfortunately, I don't have the time to sit down
and write a really good SQL tutorial right now that would explain everything
in adequate detail. I'm going to leave it at this for the moment in the hope
that it helps give you an idea how to proceed.
If you want any further explanation, post back to this mailing list with
specific questions and someone will probably be able to help you further.
Please don't be intimidated by this note: joins are actually pretty easy to
do. Most people understand them pretty quickly given a few examples.
Unfortunately, I just don't have the time to explain the concepts and work
through a few good examples right now. But I think you'll see that you
understand joins pretty quickly once you see a decent tutorial.
Rhino
--
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/[EMAIL PROTECTED]
