look up data within the same database

Question

I have an oracle database which contains data like employee number, employee's supervisor's employee number, employee name.

I'm looking to extract data from this table in a way such that if I'm selecting the name of the employee and the employee's supervisor's employee number, then how do I go about listing out the supervisor's name in the same query?

For example:

emp_number ---- emp_name ---- emp_supervisor_emp_number
100 ............ abc ............321
200 ............ xyz ............920
321 ............ pqr ............454
920 ............ klk ............344

Now I want to be able to extract data like so (1 level up):

100............ abc ............321 ..... pqr
200............ xyz ............920 ..... klk

score 0 · Answer 1 · answered Feb 27 '18 at 20:55

Self-join is one option:

SQL> with emp (emp_number, emp_name, emp_supervisor) as
  2    (select 100, 'abc', 321 from dual union
  3     select 200, 'xyz', 920 from dual union
  4     select 321, 'pqr', 454 from dual union
  5     select 920, 'klk', 344 from dual
  6    )
  7  select e1.emp_number,
  8    e1.emp_name,
  9    e1.emp_supervisor,
 10    e2.emp_name
 11  from emp e1 join emp e2 on e2.emp_number = e1.emp_supervisor
 12  order by e1.emp_number;

EMP_NUMBER EMP EMP_SUPERVISOR EMP
---------- --- -------------- ---
       100 abc            321 pqr
       200 xyz            920 klk

SQL>

score 0 · Accepted Answer · answered Feb 27 '18 at 21:14

0

Another self-join option using the left-join and "NULL" management.

    select a.empid,
       a.name,
       b.empid,
       b.name
  from em1 a,
       em1 b
 where a.suid = b.empid(+)
   and b.empid is not null

 EMPID NAME EMPID NAME

   100 abc  321   pqr
   200 xyz  920   klk

answered Feb 27 '18 at 21:14

Ozymandias

36
2

It is not recommended to use the (+) syntax of Oracle : Read https://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation – Kaushik Nayak Feb 28 '18 at 04:32
Regardless of syntax, left join on itself is easy to write. Thanks for the link. – Ozymandias Feb 28 '18 at 05:03

look up data within the same database

2 Answers2