Case statements with Join on lookup-CodePudding

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