CODEWITHSUNDEEP
sqloracle-database
user3809240
user3809240

Reputation: 93

SQL Query to roll down the manager hierarchy

The scenario is :- Joyce is a MGR, reporting to Pam. Greg is a MGR reporting to Joyce, thus rolling up to Pam.

So for a very high level person,you will see all the reportees directly or indirectly related to him with the query.

Table with the information TABLE_MGR :-

Employee    MGR

Joyce       PAM
GREG        JOYCE

So the desired output will be as 

 Employee    MGR

Joyce       PAM
GREG        PAM

How can this be achieved ?

Upvotes: 0

Views: 1970

Answers (1)

hol
hol

Reputation: 8423

You can use the connect by clause together with connect_by_root.

Have a look into the Oracle Documentation.

Example

create table tmp_emp as  
 select 'JOYCE' employee, 'PAM' mgr from dual
 union
 select 'GREG' employee, 'JOYCE' mgr from dual;

select level, CONNECT_BY_root mgr root_mgr, t.*
from tmp_emp t
start with mgr = 'PAM'
connect by nocycle prior employee = mgr;

Output

     LEVEL ROOT_MGR EMPLOYEE MGR  
---------- -------- -------- -----
         1 PAM      JOYCE    PAM  
         2 PAM      GREG     JOYCE

2 rows selected.

Edit:

There is no need to use the start with. This will also work

select level, CONNECT_BY_root mgr root_mgr, t.*
from tmp_emp t
connect by nocycle prior employee = mgr;

But maybe you only want the employees with no manager you could do this. It also shows you the hierarchy to the top manager.

with roots as 
(
   select level lvl, CONNECT_BY_root mgr root_mgr, employee,  SYS_CONNECT_BY_PATH(mgr, '/') "Path"
   from tmp_emp t
   connect by  prior employee = mgr
)
select * 
from roots r1 
where root_mgr not in (select employee from roots r2 );

Output

       LVL ROOT_MGR EMPLOYEE Path          
---------- -------- -------- -------------
         2 PAM      GREG     /PAM/JOYCE
         1 PAM      JOYCE    /PAM

Upvotes: 1

Related Questions