Thank you for the table structures (I prefer the output from SHOW CREATE TABLE......) Now, would you mind also posting the actual query you used to produce what you are calling "duplicated" results?
Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Kevin Fricke" <[EMAIL PROTECTED]> wrote on 12/05/2005 09:11:54 PM: > 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] >
