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?

Answer 1

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