Direct and Indirect Reports for All Managers

Transact-SQL
1

I have a table with EmpID, EmpName, DeptName, ManagerID --- What I need a query where I can get Manager's Name, Department and all direct and indirect reports of the manager. The query will have lot more rows than source table as Each employee can be linked directly to a supervisor and then indirectly to supervisors of the direct supervisor. A supervisor will have multiple rows, one row each for direct reports and row each for each of the indirect reports; the level is multiple level deep... Any help will be appreciated. Thank You.

3

hope this link helps .. you can google search also .. lots of articles

5

it might be easier to do this
PROGRAMATICALLY
with a series of statements
preferably in a stored procedure

instead of one SQL Statement

is that OK ?

6

Thanks Harishgg1 - yes that should be ok.

7

Would you please post the sample data as CREATE TABLE and INSERT statement(s) that are directly usable rather than as a screen image?

10

hi

i tried to do this through a series of SQL statements and UNION ALL

there are some major issues with what i did

for example .. i hard coded = managerid is null and managerid =1

another thing .. is what about 8 iterations ( the code becomes humungous if i do this this way )

-- Lot of further work is left .. maybe a different approach

but any how ..something to go upon

please click arrow to the left for SQL script 
SELECT 
      a.empid
	 ,a.EmpName
	 ,a.DeptName
	 ,b.EmpID as EmpID1 
FROM 
    ( SELECT * FROM Employee WHERE ManagerID is null ) a JOIN Employee b on b.ManagerID = a.EmpID
UNION ALL
SELECT a.* FROM 
(
SELECT 
   a.empid,a.EmpName,a.DeptName,b.EmpID as EmpID1
FROM 
    (
     SELECT a.empid,a.EmpName,a.DeptName,b.EmpID as EmpID1 FROM ( SELECT * FROM Employee WHERE ManagerID is null ) a JOIN Employee b on b.ManagerID = a.EmpID
     ) a join Employee b on b.ManagerID = a.EmpID1
) a join Employee b on b.ManagerID = a.EmpID1
UNION ALL
SELECT a.* FROM 
(
SELECT a.empid,a.EmpName,a.DeptName,b.EmpID as EmpID1
FROM 
(
SELECT 
   a.empid,a.EmpName,a.DeptName,b.EmpID as EmpID1
FROM 
    (
     SELECT a.empid,a.EmpName,a.DeptName,b.EmpID as EmpID1 FROM ( SELECT * FROM Employee WHERE ManagerID is null ) a JOIN Employee b on b.ManagerID = a.EmpID
     ) a join Employee b on b.ManagerID = a.EmpID1
) a join Employee b on b.ManagerID = a.EmpID1
) a join Employee b on b.ManagerID = a.EmpID
union all 
SELECT 
      a.empid
	 ,a.EmpName
	 ,a.DeptName
	 ,b.EmpID as EmpID1 
FROM 
    ( SELECT * FROM Employee WHERE Empid =1  ) a JOIN Employee b on b.ManagerID = a.EmpID
UNION ALL
SELECT 
 a.empid
	 ,a.EmpName
	 ,a.DeptName
	 ,b.EmpID as EmpID1 
FROM 
(
SELECT 
      a.empid
	 ,a.EmpName
	 ,a.DeptName
	 ,b.EmpID as EmpID1 
FROM 
    ( SELECT * FROM Employee WHERE Empid =1  ) a JOIN Employee b on b.ManagerID = a.EmpID
	) a JOIN Employee b on b.ManagerID = a.EmpID1

image

11

hi

looking at alternative ways .. this is another attempt

image
image438×708 10.8 KB