Need help in SQL join

You need to join the code table twice.

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

You just need to join the tables properly.

Table Data

SQL> SELECT * FROM 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      171127 rows selected.SQL> SELECT * FROM code;    CODEID CODENAME---------- --------     17112 200TS     17113 400TS     17114 100TS     17115 500TS     17116 620TS     17117 899TS     17118 900TS     16800 888TS     18000 912TS9 rows selected.

Query

Using Oracle join syntax:

SQL> column code_one format a8SQL> column code_two format a8SQL> SELECT E.employeeid,  2    C1.codename AS code_one,  3    C2.codename AS code_two  4  FROM Employee e,  5    code c1,  6    code c2  7  WHERE E.code_one = c1.codeid  8  AND E.code_two   = c2.codeid  9  /EMPLOYEEID CODE_ONE CODE_TWO---------- -------- --------       108 900TS    200TS       106 620TS    200TS       103 100TS    200TS       102 400TS    200TS       101 200TS    200TS       104 500TS    888TS       107 899TS    912TS7 rows selected.SQL>

Using ANSI join syntax:

SQL> SELECT E.employeeid,  2    C1.codename AS code_one,  3    C2.codename AS code_two  4  FROM Employee e  5  INNER JOIN code c1  6  ON E.code_one = c1.codeid  7  INNER JOIN code c2  8  ON E.code_two = c2.codeid  9  /EMPLOYEEID CODE_ONE CODE_TWO---------- -------- --------       108 900TS    200TS       106 620TS    200TS       103 100TS    200TS       102 400TS    200TS       101 200TS    200TS       104 500TS    888TS       107 899TS    912TS7 rows selected.SQL>