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:[EMAIL PROTECTED] Sent: Monday, December 05, 2005 5:19 PM To: mysql; [EMAIL PROTECTED] 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: <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > Subj: Re: Select questions > Date: Mon Dec 5, 2005 4:00 pm > Size: 2K > To: <[EMAIL PROTECTED]>,<mysql@lists.mysql.com> > > > ----- 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: > > --- > > --- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
