Recursive join in SQL

Question

I am having below data

Current Data

Emp id  Emp Name    Manger ID
100     Employee    101
101     Team Lead   102
102     Manager     103
103     Sr. Manager 104
104     Director    105
105     VP          0

Expected Data:

Emp id  Emp Name    Manger ID   Manager Name
105     VP          0           Null
104     Director    105         VP
103     Sr. Manager 104         VP
102     Manager     103         VP
101     Team Lead   102         VP
100     Employee    101         VP

How can we achieve this ??

select c.*
  from (select a.emp_id
              ,a.emp_name
              ,a.manager_id as manager_id
              ,b.emp_name   as manager_name
          from (select emp_id
                      ,emp_name
                      ,manager_id
                  from emp_test
                 WHERE manager_id <> 0) a
              , --parent is present                                   
               (select emp_id
                      ,emp_name
                      ,manager_id
                  from emp_test) b --all records                                  
         where a.manager_id = b.emp_id
        union all
        select emp_id
              ,emp_name
              ,manager_id as manager_id
              ,'' as manager_name
          from emp_test
         WHERE manager_id = 0) c
 ORDER BY 1 desc
         ,2 asc;

Are you sure about the manager name output? OR you want to show EmpName as ManagerName according to Manager ID — Nitika
– Nitika, Commented Apr 8, 2019 at 8:07
looks like a classic connect by prior / connect_by_root to me: docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm and docs.oracle.com/cd/B19306_01/server.102/b14200/… — Caius Jard
– Caius Jard, Commented Apr 8, 2019 at 8:08

MT0 · Accepted Answer · 2019-04-08 08:26:29Z

Use a hierarchical query and CONNECT_BY_ROOT to get the manager name at the root of the hierarchy:

Oracle Setup:

CREATE TABLE table_name ( Emp_id, Emp_Name, Manager_ID ) AS
SELECT 100,   'Employee',    101 FROM DUAL UNION ALL
SELECT 101,   'Team Lead',   102 FROM DUAL UNION ALL
SELECT 102,   'Manager',     103 FROM DUAL UNION ALL
SELECT 103,   'Sr. Manager', 104 FROM DUAL UNION ALL
SELECT 104,   'Director',    105 FROM DUAL UNION ALL
SELECT 105,   'VP',          0 FROM DUAL;

Query:

SELECT t.*,
       CASE WHEN Manager_id <> 0 THEN CONNECT_BY_ROOT( Emp_Name ) END AS Manager_Name
FROM   table_name t
START WITH Manager_Id = 0
CONNECT BY PRIOR Emp_id = Manager_ID

Output:

EMP_ID | EMP_NAME    | MANAGER_ID | MANAGER_NAME
-----: | :---------- | ---------: | :-----------
   105 | VP          |          0 | null        
   104 | Director    |        105 | VP          
   103 | Sr. Manager |        104 | VP          
   102 | Manager     |        103 | VP          
   101 | Team Lead   |        102 | VP          
   100 | Employee    |        101 | VP

db<>fiddle here

trincot · Accepted Answer · 2019-04-08 08:26:01Z

0

You can use this query:

select emp_id, emp_name, manager_id, prior emp_name as manager_name
from emp_test
start with manager_id = 0
connect by prior emp_id = manager_id;

Note that this will output:

EMP_ID  EMP_NAME    MANAGER_ID MANAGER_NAME
105     VP          0   
104     Director    105        VP
103     Sr. Manager 104        Director
102     Manager     103        Sr. Manager
101     Team Lead   102        Manager
100     Employee    101        Team Lead

So not always "VP" in the last column, like you had it.

If the manager name should always be the top-level manager, then:

select emp_id, emp_name, manager_id,
       case when manager_id <> 0 then connect_by_root emp_name end as manager_name
from emp_test
start with manager_id = 0
connect by prior emp_id = manager_id;

Output:

EMP_ID  EMP_NAME    MANAGER_ID MANAGER_NAME
105     VP          0   
104     Director    105        VP
103     Sr. Manager 104        VP
102     Manager     103        VP
101     Team Lead   102        VP
100     Employee    101        VP

edited Apr 8, 2019 at 8:26

answered Apr 8, 2019 at 8:18

trincot

357k38 gold badges282 silver badges338 bronze badges

2 Comments

hotfix Over a year ago

your Statement does not produce the correct result. in example manager_name should be VP

trincot Over a year ago

@hotfix, see addition to my answer.

Collectives™ on Stack Overflow

Recursive join in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related