Full recursive employee-boss relation in SQL Server

Question

I need to get the name of all of the employees that depends of a person directly or indirectly. Using the query in this example (from https://rextester.com/WGVRGJ67798),

create table employee(
id int not null,
employee varchar(10) not null,
boss int null    
)

insert into employee values
(1,'Anna',null),
(2,'Bob',1),
(3,'Louis',1),
(4,'Sara',2),
(5,'Sophie',2),
(6,'John',4);

with boss as (
     select id, employee, boss, cast(null as varchar(10)) as name
     from employee 
     where boss is null
    
     union all 
    
     select e.id, e.employee, b.id, b.employee
     from employee e 
     join boss b on b.id = e.boss
 )

select * from boss

I can get this result:

However, I need to see this:

It would be like showing all the possible relations between a person an all of those employees "below" him or her.

If you google for `T-SQL hierarchical query` you'll see you need a recursive CTE for this — Panagiotis Kanavos, Sep 08 '20 at 15:02
@Larnu Ok, my question is how can I modifify the recursive CTE query to get the green part of the second image. With the cte query I'm just getting the result of the first one. That is my doubt. — d2907, Sep 08 '20 at 15:08
I have added the code from your link into your question posted above. Note that it is important for questions to be complete on their own, without critical dependencies on other web sites (unless something is way too big to be included here). This is because if the external site goes away in the future, then your question will no longer make sense to later readers who might have been helped by the answers. So it's OK to reference other websites, but use them a supplementary/helpful sources rather than necessary information. — RBarryYoung, Sep 08 '20 at 15:09
@RBarryYoung Thanks a lot for your suggestion. I will keep it in mind next time. — d2907, Sep 08 '20 at 16:32

score 5 · Answer 1 · answered Sep 08 '20 at 16:13

You can reverse the logic: instead of starting from the boss (the root) and going towards employees (the leafs), you could start from the leafs and walk toward the root. This lets you generate the intermediate relations as you go:

with cte as (
     select e.id, e.employee, e.boss, b.employee name, b.boss new_boss
     from employee e
     left  join employee b on b.id = e.boss
     union all 
     select c.id, c.employee, c.new_boss, e.employee, e.boss
     from cte c 
     join employee e on e.id = c.new_boss
)
select id, employee, boss, name 
from cte
order by id, boss

Demo on DB Fiddle:

id | employee | boss | name
-: | :------- | ---: | :---
 1 | Anna     | null | null
 2 | Bob      |    1 | Anna
 3 | Louis    |    1 | Anna
 4 | Sara     |    1 | Anna
 4 | Sara     |    2 | Bob 
 5 | Sophie   |    1 | Anna
 5 | Sophie   |    2 | Bob 
 6 | John     |    1 | Anna
 6 | John     |    2 | Bob 
 6 | John     |    4 | Sara

Thank you for this exceedingly clear and intuitive explanation. Unless I'm missing something, this should be the selected solution — dancow, Jan 26 '21 at 22:16

score 1 · Answer 2 · answered Sep 08 '20 at 16:55

I like hierarchyid for this sort of thing.

use tempdb;
drop table if exists employee;
drop table if exists #e;

create table employee(
    id int not null,
    employee varchar(10) not null,
    boss int null    
)

insert into employee values
(1,'Anna',null),
(2,'Bob',1),
(3,'Louis',1),
(4,'Sara',2),
(5,'Sophie',2),
(6,'John',4);

with boss as (
     select id, employee, boss, 
        cast(concat('/', id, '/') as hierarchyid) as h
     from employee 
     where boss is null
    
     union all 
    
     select e.id, e.employee, b.id, 
        cast(concat(b.h.ToString(), e.id, '/') as hierarchyid)
     from employee e 
     join boss b on b.id = e.boss
 )

select *
into #e
from boss

select e.id, e.employee, b.id, b.employee, b.h.ToString()
from #e as e
left join #e as b
    on e.h.IsDescendantOf(b.h) = 1
    and e.id <> b.id;

I took your code mostly as is and changed the following things:

Rather than keeping track of the boss in the recursive CTE, I'm building a hierarchyid path that leads all the way back to the root of the hierarchy.
Shoved the results of the cte into a temp table
Selected from the temp table, using a self-join where the join criteria are "where the inner table's notion of employee is anywhere in the management chain for the outer table".

Note, for the join, I'm excluding the case where the employee reports to themselves; you cannot be your own boss in this situation (even though the IsDescendantOf method would suggest otherwise!).

score 0 · Answer 3 · answered Sep 08 '20 at 15:41

Something like this. There are two recursions. First, to get the h_level which with the first recursion represent boss-->employee relationships. Second, treats each row from the first as the leaf node in a new recursion to find direct and indirect hierarchical relationships.

Data

drop table if exists Employee;
go
create table employee(
  id       int not null,
  employee varchar(10) not null,
  boss     int null)

insert into employee values
(1,'Anna',null),
(2,'Bob',1),
(3,'Louis',1),
(4,'Sara',2),
(5,'Sophie',2),
(6,'John',4);

Query

;with 
boss(id, employee, boss, h_level) as (
     select id, employee, boss, 0
     from employee 
     where boss is null
     union all 
     select e.id, e.employee, b.id, b.h_level+1
     from employee e 
          join boss b on b.id = e.boss),
downlines(id, employee, boss, h_level, d_level) as (
     select id, employee, boss, h_level, 0
     from boss 
     union all 
     select b.id, b.employee, d.id, d.h_level, d.d_level+1
     from boss b 
          join downlines d on d.id = b.boss)
select * 
from downlines
order by h_level, d_level;

Output

id  employee    boss    h_level d_level
1   Anna    NULL0   0
2   Bob     1   0   1
3   Louis   1   0   1
4   Sara    2   0   2
5   Sophie  2   0   2
6   John    4   0   3
2   Bob     1   1   0
3   Louis   1   1   0
4   Sara    2   1   1
5   Sophie  2   1   1
6   John    4   1   2
4   Sara    2   2   0
5   Sophie  2   2   0
6   John    4   2   1
6   John    4   3   0

Full recursive employee-boss relation in SQL Server

3 Answers3