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.

Answer 1

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:

1. 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.

2. Shoved the results of the cte into a temp table

3. 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!).

Answer 2

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

Answer 3

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