Need help in SQL join

Question

I have two tables as below:

Table: Employee

employeeid  code_one    code_two
101          17112       17112
102          17113       17112
103          17114       17112
104          17115       16800
106          17116       17112
107          17117       18000
108          17118       17112

Table: Code

codeid  codename
17112   200TS
17113   400TS
17114   100TS
17115   500TS
17116   620TS
17117   899TS
17118   900TS
16800   888TS
18000   912TS

I need a output like: output

employeeid  code_one    code_two
101         200TS       200TS
102         400TS       200TS
103         100TS       200TS
104         500TS       888TS
106         620TS       200TS
107         899TS       912TS
108         900TS       200TS

I need to map code id's from employee table with their corrsponding code names from code table. Please help me.

Answer 1

You need to join the code table twice.

SELECT E.employeeid,
       C.codename  AS code_one,
       C1.codename AS code_two
FROM   Employee E
       INNER JOIN Code C
               ON E.code_one = c.code
       INNER JOIN Code c1
               ON E.code_two = c.code