Multiple product joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple product joins

Author

Topic

lancepr
Starting Member

17 Posts

Posted - 2002-11-12 : 09:50:06

I have 2 tables.
The first looks like this:
ID,Name,Prod1,Prod2,Prod3,Prod4,Prod5
1 ,a ,5
2, a ,1 ,5

The second is like this
ProdID,ProdDesc
1 ,test
5, ,tmp

The ProdID is stored in the Prod1-5 fields, the problem is that all 5 products do not always have a value so when I try and do join on each field I get nothing.

How can I return all rows that name=a and it will map the ProdDesc to the Prod1-5 fields.

Thanks,
Lance

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-12 : 10:16:54

This is as far as I got. Unfortunately I think this will give you five rows for each ID. You just need to creatively get rid of the unwanted rows with some kind of group by.

SELECT

ID, name,
prod1.proddesc as prod1,
prod2.proddesc as prod2,
prod3.proddesc as prod3,
prod4.proddesc as prod4,
prod5.proddesc as prod5

from firsttable
left outer join secondtable prod1
on firsttable.prod1 = prod1.prodid
left outer join secondtable prod2
on firsttable.prod2 = prod2.prodid
left outer join secondtable prod3
on firsttable.prod3 = prod3.prodid
left outer join secondtable prod4
on firsttable.prod4 = prod4.prodid
left outer join secondtable prod5
on firsttable.prod5 = prod5.prodid

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-12 : 10:22:09

Maybe this query would be easier if your tables where normalized ....

Jay White
{0}

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-12 : 10:26:46

quote:

Maybe this query would be easier if your tables where normalized ....

Indeed. As no doubt would the other one be, in the transact-sql forum.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-13 : 17:29:37

You can start by normalizing the table, as suggested. Create a table with 5 records, call it ProductCounts. Have only 1 "Num" field in this table, a numeric field with numbers 1 to 5 in each record.

Then, do a cross join with the ProductCounts and the FirstTable, and from there do an INNER JOIN to the SecondTable:

SELECT
ID, Name, Num as ProductNumber, ProductID, ProdDesc
FROM
(SELECT Id,Name, Num, CASE Num WHEN 1 THEN Prod1 WHEN 2 Then Prod2 WHEN 3 THEN Prod3 WHEN 4 THEN Prod4 WHEN 5 THEN Prod5 END as ProductID
FROM FirstTable CROSS JOIN ProductCounts) Normalized
INNER JOIN
SecondTable ON Normalized.ProductID = SecondTable.ProductID

Not sure if this is what you are looking for (more rows, less columns), but if it is, here you go !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources