Need help in SQL join

curiousBoy Published at Dev

curiousboy

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.

Lalit Kumar B

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      17112

7 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 912TS

9 rows selected.

Query

Using Oracle join syntax:

SQL> column code_one format a8
SQL> column code_two format a8
SQL> 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    912TS

7 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    912TS

7 rows selected.

SQL>

Collected from the Internet

Please contact [email protected] to delete if infringement.