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?