How to get list of all levels of employees working under supervisor?

Question

I need to get the list of employees down to the minimum level working under a specific supervisor or head or manager.

Below is the hierarchy of employees, each employee has a supervisor, who reports to a head and who reports to a manager as below:

The data in the table has only 2 fields as follows:

My SQL Query would be SELECT Employee_ID FROM DATABASE_TABLE WHERE Supervisor_ID='Joe'. The result would be that I get the list of all employees reporting to Joe to the minimum level. Similarly, if I query for Supervisor_ID='David', I should get all employees reporting to David to the minimum level.

I had experience in Oracle in this type of query by using CONNECT_BY_PATH, but I have no idea on how to achieve this in MS SQL. An example would be highly appreciable. Thanks.

Create Table and Insert Statements:

CREATE TABLE DATABASE_TABLE(Employee_ID varchar(255),Supervisor_ID varchar(255));

INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Alice','Dave');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Olive','Dave');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Barton','Dave');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Almira','Jacob');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Charles','Jacob');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Davis','Jacob');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Robert','Risha');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Peter','Risha');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Ethel','Risha');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Isaac','Jospeh');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Sophia','Jospeh');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Rosa','Jospeh');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Joshua','Dandy');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Silas','Dandy');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Fred','Dandy');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Frank','Andrew');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Howard','Andrew');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Ralph','Andrew');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Dennis','Henry');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Alex','Henry');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Floyd','Henry');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Carlos','Nelson');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Homer','Nelson');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Harold','Nelson');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Leo','Simon');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Warren','Simon');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Clifford','Simon');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Martha','Casper');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Hazel','Casper');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Irene','Casper');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Dave','Betsy');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Jacob','Betsy');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Risha','David');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Jospeh','David');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Dandy','Phillip');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Andrew','Phillip');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Henry','Harvey');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Nelson','Harvey');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Simon','Paul');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Casper','Paul');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Betsy','Joe');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('David','Joe');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Phillip','Joe');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Harvey','Joe');
INSERT INTO DATABASE_TABLE(Employee_ID, Supervisor_ID) VALUES ('Paul','Joe');

Images of data are useless to those you are asking for help from. Supply data as tabular formatted `text` or DDL and DML statements. Don't ask the users to transcribe it all; we won't. Why have you tagged 3 different versions of SQL Server? 2008 and 2008 are **completely** unsupported and 2012 only has extended support. What version are you *actually* using? Please edit your tag and only tag *that* version. — Thom A, Dec 23 '19 at 12:05
this post on cte recursion should help. https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Joe, Dec 23 '19 at 12:33
Maybe it's just me. But when I see a column that ends with `Id`, then I would expect that it contains integers or guids. With the names contained in referenced table(s) (f.e. T_Employees, T_Supervisors). — LukStorms, Dec 23 '19 at 12:50
Ok, makes sense. Btw, both sql server and oracle db support Recursive CTE's. Which can be used to traverse through an adjecency list like your table. Check that link from JoeDBA_HAHAHA for example. — LukStorms, Dec 23 '19 at 13:03

LukStorms · Accepted Answer · 2019-12-23T14:13:26.497

Here's an example that will give you some inspiration about how a recursive CTE can be used for this.

Sample data:

CREATE TABLE DATABASE_TABLE 
(
  Employee_Name nvarchar(255) PRIMARY KEY,
  Supervisor_Name nvarchar(255) NOT NULL
);

CREATE INDEX [ix_database_table_supervisor]
    ON DATABASE_TABLE (Supervisor_Name);
GO

INSERT INTO DATABASE_TABLE
(Employee_Name, Supervisor_Name) VALUES 
  ('Alice','Dave')
, ('Olive','Dave')
, ('Barton','Dave')
, ('Almira','Jacob')
, ('Charles','Jacob')
, ('Davis','Jacob')
, ('Robert','Risha')
, ('Peter','Risha')
, ('Ethel','Risha')
, ('Isaac','Jospeh')
, ('Sophia','Jospeh')
, ('Rosa','Jospeh')
, ('Joshua','Dandy')
, ('Silas','Dandy')
, ('Fred','Dandy')
, ('Frank','Andrew')
, ('Howard','Andrew')
, ('Ralph','Andrew')
, ('Dennis','Henry')
, ('Alex','Henry')
, ('Floyd','Henry')
, ('Carlos','Nelson')
, ('Homer','Nelson')
, ('Harold','Nelson')
, ('Leo','Simon')
, ('Warren','Simon')
, ('Clifford','Simon')
, ('Martha','Casper')
, ('Hazel','Casper')
, ('Irene','Casper')
, ('Dave','Betsy')
, ('Jacob','Betsy')
, ('Risha','David')
, ('Jospeh','David')
, ('Dandy','Phillip')
, ('Andrew','Phillip')
, ('Henry','Harvey')
, ('Nelson','Harvey')
, ('Simon','Paul')
, ('Casper','Paul')
, ('Betsy','Joe')
, ('David','Joe')
, ('Phillip','Joe')
, ('Harvey','Joe')
, ('Paul','Joe')

Query:

DECLARE @HeadName nvarchar(255);
SET @HeadName = 'David';

DECLARE @MaxLvl INT = 2;


WITH RCTE_DATA AS
(
   -- seeding the recursion
   SELECT
   t.Employee_Name AS Head_Name,
   t.Supervisor_Name AS Manager_Name,
   0 AS Lvl,
   t.Employee_Name, 
   t.Supervisor_Name
   FROM DATABASE_TABLE t
   WHERE t.Employee_Name = @HeadName

   UNION ALL

   -- looping the recursion  
   SELECT 
   c.Head_Name,
   c.Manager_Name,
   c.Lvl + 1,
   t.Employee_Name, 
   t.Supervisor_Name
   FROM RCTE_DATA c
   JOIN DATABASE_TABLE t
     ON t.Supervisor_Name = c.Employee_Name
   WHERE c.Lvl < @MaxLvl
)
SELECT 
Employee_Name, 
Supervisor_Name, 
Head_Name,
Manager_Name
FROM RCTE_DATA
WHERE Lvl = @MaxLvl

Results:

Employee_Name | Supervisor_Name | Head_Name | Manager_Name
:------------ | :-------------- | :-------- | :-----------
Ethel         | Risha           | David     | Joe         
Peter         | Risha           | David     | Joe         
Robert        | Risha           | David     | Joe         
Isaac         | Jospeh          | David     | Joe         
Rosa          | Jospeh          | David     | Joe         
Sophia        | Jospeh          | David     | Joe

db<>fiddle here

score 0 · Answer 2 · answered Nov 19 '21 at 03:28

select * from  (

SELECT a.Employee_Name, a.Supervisor_Name, b.Supervisor_Name as Head_Name,  c.Supervisor_Name as Manager_Name
FROM DATABASE_TABLE a
LEFT JOIN DATABASE_TABLE b ON a.Supervisor_Name = b.Employee_Name
LEFT JOIN DATABASE_TABLE c ON b.Supervisor_Name = c.Employee_Name) as t

where t.Head_Name='David'
order by Supervisor_Name ;

How to get list of all levels of employees working under supervisor?

2 Answers2