Inner Join to match multiple rows in lookup table
-
So. I'm trying to match multiple codes (in one record) to their descriptions as listed in another table. So I have... TABLE 1 with columns: Key, Code1, Code2 TABLE 2 with columns: Code, Description ...which are tables I have no control over. I want to do an inner join so that my new table will have columns... Key, Code1, Code1Description, Code2, Code2Description. Its easy enought to match one
SELECT Table1.*, Table2.description AS Code1Description FROM Table1 INNER JOIN Table2 ON Table1.Code1=Table2.CodeHow do i expand this to 2 or 3 or 4 different codes??? Please? -
So. I'm trying to match multiple codes (in one record) to their descriptions as listed in another table. So I have... TABLE 1 with columns: Key, Code1, Code2 TABLE 2 with columns: Code, Description ...which are tables I have no control over. I want to do an inner join so that my new table will have columns... Key, Code1, Code1Description, Code2, Code2Description. Its easy enought to match one
SELECT Table1.*, Table2.description AS Code1Description FROM Table1 INNER JOIN Table2 ON Table1.Code1=Table2.CodeHow do i expand this to 2 or 3 or 4 different codes??? Please?The same way you did it for the first column. ie: adding a second would give:
SELECT t1.*, c1.description AS Code1Description, c2.description AS Code2Description
FROM Table1 t1
INNER JOIN Table2 c1 ON t1.Code1=c1.Code
INNER JOIN Table2 c2 ON t1.Code2=c2.CodeWith this you should be able to add as many code you would like.
Wout Louwers
-
The same way you did it for the first column. ie: adding a second would give:
SELECT t1.*, c1.description AS Code1Description, c2.description AS Code2Description
FROM Table1 t1
INNER JOIN Table2 c1 ON t1.Code1=c1.Code
INNER JOIN Table2 c2 ON t1.Code2=c2.CodeWith this you should be able to add as many code you would like.
Wout Louwers
Thank you very much. Never would have got that on my own. But on some servers the description table is empty, and i'm not getting any return rows. How can i make it optional, to say Return row for key='123' and add the descriptions IF they exist in the descriptions table?