how do I join on the same column multiple times, one of the tables is a configuration table with values and other is the data table.
T1:
ID Seq Code1 Code2 Code3 1 001 101 203 305 1 002 107 2 001 103 3 005 213 4 009 320 5 001 101 314
T2 (Config table):
Value Description 101 Strawberry 103 Raspberry 107 Blueberry 111 Banana 203 Cashews 213 Almonds 305 Bellpepper 320 Tomatoes 314 Potatoes
I need to display the code and its relevant description for each ID.
Expected Output:
ID Code1 Description Code2 Description Code3 Description 1 101 Strawberry 203 Cashews 305 Bellpepper 1 107 Blueberry 2 103 Raspberry 3 213 Almonds 4 320 Tomatoes 5 101 Strawberry 314 Potatoes
This is what I have tried so far, however, it is not giving me the desired output:
select distinct ID,code1, T2.description,
code2, T2.description, code3, T2.description
from T1,T2
where (T1.Code1=T2.Value OR T1.Code2=T2.Value or T1.Code3=T2.Value)
How can I achieve this? Sorry if my question is confusing, I can provide more details if needed.
Advertisement
Answer
That’s multiple outer join with the t2 table:
select a.id, a.seq,
a.code1, d1.description,
a.code2, d2.description,
a.code3, d3.description
from t1 a left join t2 d1 on a.code1 = d1.value
left join t2 d2 on a.code2 = d2.value
left join t2 d3 on a.code3 = d3.value
order by a.id, a.seq;
ID SEQ CODE1 DESCRIPTIO CODE2 DESCRIPTIO CODE3 DESCRIPTIO
----- --- ---------- ---------- ---------- ---------- ---------- ----------
1 001 101 Strawberry 203 Cashews 305 Bellpepper
1 002 107 Blueberry
2 001 103 Raspberry
3 005 213 Almonds
4 009 320 Tomatoes
5 001 101 Strawberry 314 Potatoes