I want to get a list of employees who come under the given Head/Employee.

It should return all sub-heads and their sub-head and employees under him/her.

Following is the list of all Employees with their designations.

Lets say we ask Who comes under 'CountryHead-UK'?
It should return

If we ask who should come under CEO, then it should return everyone (for this sample data)

DROP TABLE IF EXISTS #A
CREATE TABLE #A (EmpID int,Name VARCHAR(MAX), Designation INT, HeadEmpID INT)
INSERT INTO #A VALUES (1,'CEO',1,0)
INSERT INTO #A VALUES (2,'CountryHead-USA',2,1)
INSERT INTO #A VALUES (3,'CountryHead-UK',2,1)

INSERT INTO #A VALUES (4,'StateHead-USA-A',3,2)
INSERT INTO #A VALUES (5,'StateHead-USA-B',3,2)
INSERT INTO #A VALUES (6,'StateHead-UK-C',3,3)

INSERT INTO #A VALUES (7,'ZoneHead-A',9,6)
INSERT INTO #A VALUES (8,'ZoneHead-B',9,6)
INSERT INTO #A VALUES (9,'ZoneHead-C',9,4)

INSERT INTO #A VALUES (10,'CityHead-A',12,7)

SELECT * FROM #A a

Fiddle: https://dbfiddle.uk/YZH65Xvi

CodePudding user response：

Thanks Larnu for a hint.

Follwing worked.

with cte as (
     select e.Empid, e.Name, e.Head, b.Name N1, b.Head new_boss
     from #a e
     left  join #a b on b.Empid = e.Head
     union all 
     select c.Empid, c.Name, c.new_boss, e.name, e.head
     from cte c 
     join #a e on e.Empid = c.new_boss
)

select Empid, Name, Head, N1 
from cte WHERE n1 = 'CountryHead-UK'
order by Empid, head