Case statements with Join on lookup

Cheryl Waller Tech December 11, 2021

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?

Written by Cheryl Waller