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

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