Joining two tables - multiple columns to single column

Question

Table 1
-------
LANG_VALUE
LANG_DESC

Table 2
-------
EmpId
LANG1
LANG2
LANG3
LANG4
LANG5
LANG6

It looks like as below:

Table 1
-------

LANGVALUE LANGDESC
-------------------
SPAN      SPANISH
GERM      GERMAN

Table 2
-------
EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6
-----------------------------------------
1     SPAN  NULL  NULL  NULL  NULL  NULL
2     GERM  SPAN  NULL  NULL  NULL  NULL
3     GERM  NULL  NULL  NULL  NULL  NULL

Expected Result:

EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6
-----------------------------------------
1     SPANISH NULL ...
2     GERMAN  SPANISH NULL...
3     GERMAN  NULL...

How to do this in sql query?

Answer 1

SELECT EmpId, 
    K1.LANGDESC AS LANG1, 
    K2.LANGDESC AS LANG2, 
    K3.LANGDESC AS LANG3, 
    K4.LANGDESC AS LANG4, 
    K5.LANGDESC AS LANG5, 
    K6.LANGDESC AS LANG6 
FROM Table2 T
LEFT JOIN Table1 K1 ON K1.LANGVALUE = T.LANG1
LEFT JOIN Table2 K2 ON K2.LANGVALUE = T.LANG2
LEFT JOIN Table3 K3 ON K3.LANGVALUE = T.LANG3
LEFT JOIN Table4 K4 ON K4.LANGVALUE = T.LANG4
LEFT JOIN Table5 K5 ON K5.LANGVALUE = T.LANG5
LEFT JOIN Table6 K6 ON K6.LANGVALUE = T.LANG6

WHERE...

Note: this unsolicited in the question, but it may be a good idea to consider a schema where the Employee to Language is stored in a separate table, in a one-to-many fashion. This will allow having employees with more than 6 languages (hope you have many of these...) as well as not carrying so many nulls for employees that ain't so polyglot, and more importantly it would allow querying say all employees with a particular language without having to worry if that is to be found in the LANG2, or LANG3 etc...

Note, the Employee-Langage table could also have an attribute indicating the "order" (or the level of fluency), so that you can also search for employees who's first listed language is x.

Answer 2

I think you'd have to do this with subqueries based on the format of your table 2. I have the distinct feeling you aren't properly normalized for table-2, which is why you have this problem in the first place.

select EmpId,(Select LANGDESC from [Table 1] t1 where t2.Lang1=LANGVALUE) as LANG1,(and so on) where from [Table 2] t2