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

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 Will >===== 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/ ------------------------------------------------------------