How can I create a view using a table which has multiple foreign key referencing the same table and a single field. I have product table and Reference table I have around 5 foreign key in product table referencing to the RefCodeKey Field in reference table. How can I create a view which shows product reference Code joining product and Reference Code
I have a product table as follows
PK PTK PC PN RCKey PSKey PCKey PCAKey
1 1 500000 Prod A 5 12 14 98
2 1 500001 Prod B 5 12 14 98
3 1 500002 Prod C 5 11 13 145
4 4 500002 Prod C 10 11 13 76
5 3 500002 Prod C 10 11 13 95
6 1 500005 Prod D 5 12 14 137
I have Reference Code Table as follows
RefCodeKey RefCodeType Code Label Status
1 ParentTypeKey assembly assembly Active
2 ParentTypeKey WHL WHL Active
3 ParentTypeKey TIRE TIRE Active
4 ParentTypeKey TIRE TIRE Active
5 RegionCodeKey 1 COMP 1 Active
6 RegionCodeKey 2 COMP 2 Active
7 RegionCodeKey 3 COMP 3 Active
8 RegionCodeKey 4 COMP 4 Active
9 RegionCodeKey 9 COMP 5 Active
10 RegionCodeKey 0 COMP 6 Active
11 ProductStatusKey CLOSED CLOSED Active
12 ProductStatusKey ACTIVE ACTIVE Active
13 ProductClassificationKey DropShip DropShipActive
14 ProductClassificationKey INFO NA INFO NA Active
How can i create a view display a result as show below?
PC PN RCKey PSKey PCKey
500000 Prod A COMP 1 ACTIVE INFO NA
500001 Prod B COMP 1 ACTIVE INFO NA
500002 Prod C COMP 1 CLOSED DropShip
500002 Prod C COMP 6 CLOSED DropShip
500002 Prod C COMP 6 CLOSED DropShip
500005 Prod D COMP 1 ACTIVE INFO NA
This is a common reporting pattern wherever the database architect has employed the "one true lookup table" model. I'm not going to get bogged down in the merits of that design. People like Celko and Phil Factor are far more erudite than me at commenting on these things. All I'll say is that having reported off over sixty enterprise databases in the last 15 years, that design is pervasive. Rightly or wrongly, you're probably going to see it over and over again.
There is currently insufficient information to definitively answer your question. The answer below makes assumptions on what I think is the most likely missing information is.
I'll assume that the foreign keys in the PRODUCT table are nullable. This affects whether we INNER JOIN or LEFT JOIN.
SELECT prod.PC
,prod.PN
,reg_code.label as RCKey
,prod_stat.label as PSKey
,prod_clas.label as PCKey
FROM PRODUCT prod
LEFT JOIN REFS reg_code ON prod.RCKey = reg_code.RefCodeKey
LEFT JOIN REFS prod_stat ON prod.PSKey = prod_stat.RefCodeKey
LEFT JOIN REFS prod_clas ON prod.PCKey = prod_clas.RefCodeKey
;
The trick is that you can refer to the REFS table as many times as you like. You just need to give it a different alias and join it to the relevant FK each time. For example reg_code is an alias. Give your aliases meaningful names to keep your code readable.
Note: Those RCKey/PSKey/PCKey names are really not good names. They'll come back to bite you. They don't represent the key. They represent a description of the thing in question. If it's a region code, call it region_code
The reason I'm assuming the REFS table is relatively small, is that if it's really large (I've seen one with 6 million lookup values across hundreds of codesets) and indexed to take RefCodeType into consideration, you might get better performance by adding a filter for RefCodeType to each of your LEFT JOINs. For example:
LEFT JOIN REFS prod_clas ON prod.PCKey = prod_clas.RefCodeKey
AND prod_clas.RefCodeType = 'ProductClassificationKey'