Select Query with Recursion

Question

I have two tables containing employee names (TableB) and Employee hierarchy( TableA) (manager_id from TableA can be employee_id in the same table ).

TableA
UniqueId    Employee_ID Manager_ID
1            101         102
2            102         103
3            103         104
4            105         106
5            106         null


TableB
Employee_ID  Employee_Name
101          First
102          Second
103          Third
104          Fourth
105          Fifth
106          Sixth

and I need output as below :

Employee_ID Employee_Name   Transferred
101         First           True
102         Second          True
103         Third           True
105         Fifth           False
106         Sixth           False

The Transferred column for each employee is calculated as =

isTransferred(Employee_ID)
{
    If(Manager_ID  is null) return false;
    If(Manager_ID is present as employee_id in table A)
    {
       return isTransferred(manager_ID)
    }
    else
    {
       return true;
    }
}

Is there any way to get the result in one select statement?

@GMB Row with 104 as employee_id is not present in TableA. The output should contain only TableA's employee_ids. — Jaanu
– Jaanu, Commented Feb 6, 2020 at 17:33
Sixth is non-transferred since they don't have a boss Fifth is non-transferred since their boss (106) is in TableB and is non-transferred Third is ... oh, no, we need to check the boss (104) 104, Fourth, is transferred since their boss is not in TableB so Third is transferred since their boss (104) is in TableB but and transferred. Good question. I see there is an answer posted but I have to come up with my own. — user1683793
– user1683793, Commented Feb 6, 2020 at 17:51
I have been playing with this back and forth and I really think you are not going to get this to happen in vanilla SQL since the employee/manager tree can be of arbitrary depth. I will be interested to see if somebody comes up with one that works but I don't see it happening right now. I'll play with it later and see if I can find a solution or prove it can't be done. — user1683793
– user1683793, Commented Feb 6, 2020 at 18:35

The Impaler · Accepted Answer · 2020-02-06 21:48:25Z

2

You can use a Recursive CTE and then get the last level of "recursion" for each employee. Once you have that, you just check the manager_id of that last level to find out if it's transferred.

For example:

with
tablea as (
  select 1 as uniqueId, 101 as employee_id, 102 as manager_id from dual union all
  select 2  as uniqueId, 102 as employee_id, 103 as manager_id from dual union all
  select 3  as uniqueId, 103 as employee_id, 104 as manager_id from dual union all
  select 4  as uniqueId, 105 as employee_id, 106 as manager_id from dual union all
  select 5  as uniqueId ,106 as employee_id, null from dual 
),
tableb as (
  select 101 as employee_id, 'first' as employee_name from dual union all
  select 102 as employee_id, 'second' as employee_name from dual union all
  select 103 as employee_id, 'third' as employee_name from dual union all
  select 104 as employee_id, 'fourth' as employee_name from dual union all
  select 105 as employee_id, 'fifth' as employee_name from dual union all
  select 106 as employee_id, 'sixth' as employee_name from dual 
),
n (employee_id, employee_name, lvl, manager_id) as (
  select b.employee_id, b.employee_name, 1, a.manager_id
  from tablea a
  join tableb b on a.employee_id = b.employee_id
union all
  select
    n.employee_id, n.employee_name, lvl + 1, a.manager_id
  from n
  join tablea a on a.employee_id = n.manager_id
),
m (employee_id, max_lvl) as (
  select employee_id, max(lvl) from n group by employee_id
)
select n.employee_id, n.employee_name, 
  case when n.manager_id is not null then 'True' else 'False' end as transferred
from n
join m on n.employee_id = m.employee_id and n.lvl = m.max_lvl
order by n.employee_id

Result:

EMPLOYEE_ID  EMPLOYEE_NAME  TRANSFERRED
-----------  -------------  -----------
        101  first          True       
        102  second         True       
        103  third          True       
        105  fifth          False      
        106  sixth          False

answered Feb 6, 2020 at 21:48

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1683793 Over a year ago

Very good. The secret is the recursive table :

n (employee_id, employee_name, lvl, manager_id) as (       select b.employee_id, b.employee_name, 1, a.manager_id       from tablea a       join tableb b on a.employee_id = b.employee_id     union all       select         n.employee_id, n.employee_name, lvl + 1, a.manager_id       from n       join tablea a on a.employee_id = n.manager_id     )

where we are adding values from N while creating N, incrementing the level each time. Thanks for a neat problem and a neater solution.

Chris Saxon · Accepted Answer · 2020-02-07 10:34:06Z

You can do it with a single pass through the tree as follows:

Outer join the management chain to the employees
Find the employees with no manager by mapping the unique_id to an "impossible" value, e.g. -1
Use the results of this join to walk down the tree, starting with the rows where the manager is null
Get the value for the expression in step 2 for the root rows; if this is the "impossible" value, then transfer => true
Filter out the rows for the impossible value

Which gives something like:

with table_a ( UniqueId, Employee_ID, Manager_ID ) as (
  select 1, 101, 102 from dual union all 
  select 2, 102, 103 from dual union all 
  select 3, 103, 104 from dual union all
  select 4, 105, 106 from dual union all 
  select 5, 106, null from dual 
), Table_b ( Employee_ID, Employee_Name ) as (
  select 101, 'First' from dual union all
  select 102, 'Second' from dual union all
  select 103, 'Third' from dual union all
  select 104, 'Fourth' from dual union all
  select 105, 'Fifth' from dual union all
  select 106, 'Sixth' from dual
), rws as (
  select b.*, a.Manager_ID,
         nvl ( a.UniqueId, -1 ) transfer
  from   table_b b
  left join table_a a
  on   b.Employee_ID = a.Employee_ID
)
  select r.*, 
         case connect_by_root transfer
           when -1 then 'true'
           else 'false'
         end transferred
  from   rws r
  where  transfer > 0
  start  with manager_id is null
  connect by manager_id = prior employee_id;

EMPLOYEE_ID    EMPLOYEE_NAME    MANAGER_ID    TRANSFER    TRANSFERRED   
           103 Third                      104           3 true           
           102 Second                     103           2 true           
           101 First                      102           1 true           
           106 Sixth                   <null>           5 false          
           105 Fifth                      106           4 false

Collectives™ on Stack Overflow

Select Query with Recursion

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related