Home > Back-end >  Case statements with Join on lookup
Case statements with Join on lookup

Time:12-21

I have a big table with some columns having lookup values. I want to create a description column for each lookup column and populate them with description from lookup table.

Here is one example

main table

select id1, id2, id3 from idtable;
id1 id2 id3
1 2 3
1 3 4

look up table

select code, id, desc from lookup;
code id desc
id1 1 id1-desc1
id2 2 id2-desc2
id2 3 id2-desc3
id3 3 id3-desc3
id3 4 id3-desc4

I want the result set like:

id1 id1desc id2 id2desc id3 id3desc
1 id1-desc1 2 id2-desc2 3 id3-desc3
1 id1-desc1 3 id2-desc3 4 id3-desc4

What is the best and most efficient way to write the SQL solution for this?

CodePudding user response:

You need three joins:

SELECT id1, l1.desc AS id1desc,
       id2, l2.desc AS id2desc,
       id3, l3.desc AS id3desc
FROM   idtable i
JOIN   lookup l1 ON id1 = l1.id
JOIN   lookup l2 ON id2 = l2.id
JOIN   lookup l2 ON id3 = l3.id
  • Related