Clever Geek Handbook

Need help connecting SQL

I have two tables as shown 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 an output: 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 match the code id from the employee table with their correcting code names from the code table. Please help me.

+3


source to share


2 answers


You just need to join the tables correctly.

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>
-1


source


You need the join

table code

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
+3


source






More articles:


All Articles