Select Field based on Multiple Field Left Join

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 2005 Forums

Transact-SQL (2005)

Select Field based on Multiple Field Left Join

Author

Topic

kfblake
Starting Member

4 Posts

Posted - 2009-01-19 : 13:21:28

Hey guys,

I could really use some help here. I'm locked into using a table structure that is making things very difficult. Basically, I have products and categories. But, those products can appear in multiple categories. Which categories each product should appear in are defined in a table with a structure like:

ProductCode
CategoryID1
DisplayOrder1
CategoryID2
DisplayOrder2
CategoryID3
DisplayOrder3
CategoryID4
DisplayOrder4
CategoryID5
DisplayOrder5

I think this is a horrible way to do this, but, like I said, it wasn't my creation and I can't change it.

I need to execute a query that uses this table to display multiple results as indicated by the values in the table. So, Product A might be in Category A, Category B and Category C. And if so, in this table, the entry for this row in the table described above might be:

ProductCodeA
A
1
B
3
C
4
null
null
null
null

The following sql executes the Category portion of this properly, but I am having a problem coordinating which DisplayOrder field to return:

SELECT ...
...product table...
LEFT JOIN ProductCategories
ON Categories.CategoryID = ProductCategories.CategoryID1
OR Categories.CategoryID = ProductCategories.CategoryID2
OR Categories.CategoryID = ProductCategories.CategoryID3
OR Categories.CategoryID = ProductCategories.CategoryID4
OR Categories.CategoryID = ProductCategories.CategoryID5

If the join is using CategoryID2, I need to select DisplayOrder2, if it uses CategoryID3, I need to select DisplayOrder3.

I'm at the end of my rope. I've thought of working the COALESE function in, but that won't work, because this returns all DisplayOrder fields and may not be using the first one that is null (they might all have values but I only want to use whichever one the join is associating with).

Any thoughts?

This is totally fubar, so let me know if I need to explain the situation further.

keith

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-19 : 14:29:38

"If the join is using CategoryID2, I need to select DisplayOrder2,.. "

DisplayOrder2 for all categories you mean ?

kfblake
Starting Member

4 Posts

Posted - 2009-01-19 : 14:35:13

DisplayOrder2 for all categories you mean ?
[/quote]

No, sorry. If the join is being performed on CategoryID3, I need to return/use DisplayOrder3 and not the other 4. The other 4 DisplayOrders may or may not have values, but I need to ignore them and only use DisplayOrder3.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-19 : 14:44:41

Can you post sample data and output so that its clear.

kfblake
Starting Member

4 Posts

Posted - 2009-01-19 : 14:57:59

Sorry, this is pretty confusing, I know.

ProductCategories Table Definition
ProductCode varchar(20)
CategoryID1 decimal
DisplayOrder1 int
...
CategoryID5 decimal
DisplayOrder5 int

Sample ProductCategories Table Data
PRODA 1.0 1 2.9 3 6.0 2 null null null null
PRODB 1.0 2 null null null null null null
PRODC 2.0 1 1.0 3 6.0 1 null null null null

What I need is to execute a query that returns the Products in each category. But, a product can be in multiple categories. So, for the above example, I need the following result:

PRODA 1.0 1
PRODB 1.0 2
PRODC 1.0 3
PRODC 2.0 1
PRODA 2.9 3
PRODC 6.0 1
PRODA 6.0 2

Is that a little more clear?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 01:18:29

Hi blake,
upto my knowledge we can't write the two unpivot statements
u have to write two select statements i think so
try this
select productcode , category,categoryval
from
ProductCategories p
unpivot
(categoryval for category in (CategoryID1,CategoryID2,CategoryID3,CategoryID4,CategoryID5)) as unpvt
select productcode , displayorder,displayorderval
from
ProductCategories p
unpivot
(displayorderval for displayorder in (DisplayOrder1,DisplayOrder2,DisplayOrder3,DisplayOrder4,DisplayOrder5)) as pvt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 02:25:09

[code]SELECT ProductCode,ID,Ord
FROM
(
SELECT ProductCode,CategoryID1 as ID,DisplayOrder1 AS Ord
FROM Table
UNION ALL
SELECT ProductCode,CategoryID2 ,DisplayOrder2
FROM Table
UNION ALL
SELECT ProductCode,CategoryID3 ,DisplayOrder3
FROM Table
UNION ALL
SELECT ProductCode,CategoryID4 ,DisplayOrder4
FROM Table
UNION ALL
SELECT ProductCode,CategoryID5 ,DisplayOrder5
FROM Table
)t
ORDER BY ID,Ord[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-20 : 06:28:09

Small change to Visakh's code:


SELECT ProductCode,ID,Ord
FROM
(
SELECT ProductCode,CategoryID1 as ID,DisplayOrder1 AS Ord
FROM Table
UNION ALL
SELECT ProductCode,CategoryID2 ,DisplayOrder2 
FROM Table
UNION ALL
SELECT ProductCode,CategoryID3 ,DisplayOrder3 
FROM Table
UNION ALL
SELECT ProductCode,CategoryID4 ,DisplayOrder4 
FROM Table
UNION ALL
SELECT ProductCode,CategoryID5 ,DisplayOrder5 
FROM Table
)t
WHERE ID IS NOT NULL and Ord IS NOT NULL
ORDER BY ID,Ord

kfblake
Starting Member

4 Posts

Posted - 2009-01-20 : 13:34:04

Looks like this works perfectly!

I think just laying it out for you to review/understand helped a ton to begin with.

Thanks for the help!

K

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:21:13

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources