RE: Need to return fewer rows than UNION (JOINs the answer?)

From: William Rice <ricew@operamail.com> > >Change capitalization where necessary > >Does this select statement give the results you are looking for? > >SELECT prod.part_id, p1.part_desc, p2.part_desc, p3.part_desc >FROM Products prod, outer Parts p1, outer Parts p2, outer Parts p3 >WHERE > prod.part1_id = p1.part_id > prod.part2_id = p2.part_id > prod.part3_id = p3.part_id It's just a guess, but it is quite likely that there are more than 3 parts in a prod. So you could quite easily wind up with: SELECT prod.part_id, p1.part_desc, p2.part_desc, p3.part_desc, p4.part_desc, p5.part_desc, p6.part_desc, p7.part_desc, p8.part_desc, p9.part_desc, p10.part_desc, p11.part_desc, p12.part_desc, .... FROM Products prod, outer Parts p1, outer Parts p2, outer Parts p3, outer Parts p4, outer Parts p5, outer Parts p6, outer Parts p7, outer Parts p8, outer Parts p9, outer Parts p10, outer Parts p11, outer Parts p12, .... WHERE prod.part1_id = p1.part_id AND prod.part2_id = p2.part_id AND prod.part3_id = p3.part_id AND prod.part4_id = p4.part_id ... ah, damn, I really can't be arsed to type the rest of this out. If you get my drift. I didn't say it couldn't be done with the current design, but it's going to be really ugly and maintenance will be even more fun. And performance will also be intriguing. Indexing, for example. Etc. > >===== Original Message From "Obnoxio The Clown" <obnoxio@hotmail.com> >===== > >From: gmurray88@aol.com (Glenn) > >> > >>I'm trying to create a view with descriptions for ID's taken from >another > >>table. > >>e.g. -- Products Table with multiple PART ID's where PART_DESC > >>is stored in Parts Table > >> > >>Products Table: > >>PROD_ ID PART1_ID PART2_ID PART3_ID > >>1 34 45 56 > > > >That's a bizarre table structure -- it ain't normal. > > > >>Parts Table > >>PART_ID PART_DESC > >>34 Widget > >>45 Digit > >>56 Gidget > >> > >>I can use a union to return 3 rows: > >> > >>PROD_ID PART_DESC > >>1 Widget > >>1 Digit > >>1 Gidget > >> > >>How can I join PRODUCTS and PARTS tables to return a single row per > >>product? > >> > >>PROD_ID PART1_DESC PART2_DESC PART3_DESC > >>1 Widget Digit Gidget > >> > >>I've tried some combinations of aliases and joins with no success yet > >>Of course there might be NULLs in any of the Part IDs above PART1_ID > >>The query should be ANSI-SQL compliant, if possible, for future > >>portability. > >>I hope it's not a problem with database design, though I'd appreciate it > >>being > >>pointed out if that's the case, since it's not too late to change it. > > > >I'd say it's a problem with database design, all right. You should rather > >have a table: > > > >PROD_ID PART_NO PART_ID > >1 1 34 > >1 2 45 > >1 3 56 > > > >Or you could go for one of those sucky, multidimensional, Pick-type > >databases that Informix now sell. <Shudder> > >________________________________________________________________________ > >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > >------------------------------------------------------------ >This e-mail has been sent to you courtesy of OperaMail, as a free >service from >Opera Software, makers of the award-winning Web Browser, Opera. Visit us >at >http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free >e-mail >account is waiting at: http://www.operamail.com/ >------------------------------------------------------------ > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Thanks for the input on this issue. It's been a few years since I've done database design and your input is helping me get back on track. The example I used of Products and Parts doesn't exactly reflect the design issues we're facing. If it were a case of secondary data (Parts) being a component of a product, then I would go with Obnoxio's redesign for his aptly stated reasons. The actual situation is that we're creating a database consisting of music CD's and CD tracks, searchable by Artist, Song, Songwriter, and more. The purpose for now is selling CDs online, also looking toward digital music online (e.g. MP3). The specific issue we're looking at is that each CD title can have several artists. There will be a maximum of about 4 before the artist becomes "Various" Also, each CD track can be composed of more than one song and have more than one artist. We could have a table for CD titles with one row per title (e.g) CD_ID TITLE Artist1_ID Artist2_ID etc... 1 Murmur 45 null 2 Three Tenors 56 47 Using Rice's suggested outer joins, a query would return one row per title (e.g.) CD_ID TITLE Artist1_Name Artist2_Name 1 Murmur REM null 2 Three Tenors Pavarotti Domingo This could be easily ordered by Artist1_Name. Using Obnoxio's suggested re-design into two tables (e.g.) CD_Titles table CD_ID TITLE 1 Murmur 2 Three Tenors CD_Artist table CD_ID Artist_ID 1 45 2 56 2 47 ... A query would return: CD_ID TITLE Artist_Name 1 Murmur REM 2 Three Tenors Pavarotti 2 Three Tenors Domingo ... This could not as easily be primarily sorted by artist because the primary sort field would have to be CD_ID. I know there are ways around this by processing the output more. But is it worth it? Maintenance, indexing, and performance are obviously big factors in the database design. Obnoxio's suggested re-design sounds more "pure" to me as ideal database design goes. Should the "pure" (normalized, relational, etc) design be strived for and the output tweaked later? Or is the more "klugy" design actually a better idea in this case. Any more ideas, esteemed colleagues? Thanks, Glenn