Join two tables by matching the two columns with different data type-CodePudding

I am trying to join two tables.

Table 1: Column 1, Column2, Language (English & French)
Table 2: Column A, Column B, Language_ID (1,2)

Language_ID & Language are matched colunms. Here, 1 refers to English, 2 refers to French. I am thinking about using case when & converting data type (numeric to varchar) when joining the tables. Can someone help me with this? Thanks a lot!

CodePudding user response：

You can go for CASE expression and convert language_id to language and do the JOIN as given below:

SELECT t1.column1,t1.column2, t2.columnA, t2.columnB
FROM       Table1 AS t1
INNER JOIN 
            (SELECT ColumnA, ColumnB, 
                    CASE Language_ID WHEN 1 THEN 'English' 
                                     WHEN 2 THEN 'French' 
                     End AS Language 
             FROM Table2) AS t2
ON t1.Language = t2.Language

CodePudding user response：

Van's answer is good -- he converts the type -- but what if there are two hundred languages? Then you would need to do something like this (but with a table not an dynamic table)

SELECT table1.Column1, table1.Column2, table2.Column1, table2.Column2
FROM table1
JOIN (
   VALUES (1, 'English'),
          (2, 'French')
) AS jTable(ID, Lang) ON table1.Language = jTable.Lang
JOIN table2 on jTable.ID = table2.Language_ID

Note -- In the real world you do this a lot -- this kind of table even has a name -- a lookup table. Normally the ID would be saved in both tables and you would link to the lookup table to get the printed value. But they can (and have) been used this way too.