Hierarchy Query in SQL Server - Get hierarchy of particular manager details

[Suryakiran]

If I select Manager Id =1 corresponding all employees 1,2,3 and 4 in the same format should get filtered. Like wise, if I select manger ID = 6, Employees 5 and 6 should get returned in the output.

Input data:

Thanks in advance for your help.

[harishgg1]

hi

if i query "managerid" greater than or equal to 6 i can get ...

-- please click arrow to the left for drop create data script

create table Data
(
EmpId int , 
EmpName varchar(100), 
ManagerID int , 
ManagerName varchar(100)
)

go 

---------------------------
-- insert into table 

insert into data select 1,'X',1,'X'
insert into data select 2,'X1',1,'X'
insert into data select 3,'X2',2,'X1'
insert into data select 4,'X3',3,'X2'
insert into data select 5,'X4',6,'Y'
insert into data select 6,'Y1',6,'Y'

SELECT 
      e.EmpId  
    , e.EmpName 
    , e.ManagerID 
    , e.ManagerName 
FROM
      data e 
	, data m
WHERE 
    e.ManagerID = m.EmpId 
AND 
    e.ManagerID >= 6

SELECT 
      e.EmpId  
    , e.EmpName 
    , e.ManagerID 
    , e.ManagerName 
FROM
      data e 
	, data m
WHERE 
    e.ManagerID = m.EmpId 
AND 
    e.ManagerID >= 3

[Suryakiran]

Hi harish,

Thanks for your reply. But your query doesn't is not giving exact solution for my requirement.

If I select Manager ID = 1, then first four rows should as directly/indirectly those employees should fall under him. I mean, for ManagerID 1, employee 1,2,3 and 4. Similarly, for ManagerID = 6, employeeID 5 and 6 should retrieve. we cannot hardcode for rows, just when selecting manageriD= 1, all corresponding employees should return.

I know this query can be achieved with CTE, but am not getting. Please try to help me.

Thanks,
Suryakiran

[harishgg1]

hi

i thought about what you said ..

the same issue for managerid 6 ...6 cannot be his own mananger

in your data .. the Top Most Manager will not have any manager above him ..
so i made it null

;WITH cte_org AS (
    SELECT       
        * 
    FROM       
        data
	WHERE 
	   ManagerID is null 
    UNION ALL
    SELECT 
       e.*
    FROM 
        data e
          INNER JOIN 
		cte_org o 
            ON o.EmpId = e.ManagerID 
     WHERE
	     e.ManagerID is not null 
)
SELECT * FROM cte_org;

[mike01]

simple cte will work

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
DROP TABLE #Data

create table #Data
(
EmpId int ,
EmpName varchar(100),
ManagerID int ,
ManagerName varchar(100)
)

go

insert into #data select 1,'X',1,'X'
insert into #data select 2,'X1',1,'X'
insert into #data select 3,'X2',2,'X1'
insert into #data select 4,'X3',3,'X2'
insert into #data select 5,'X4',6,'Y'
insert into #data select 6,'Y1',6,'Y'

declare @ManagerID int = 6

;with cte as (
select EmpID, EmpName, ManagerName, ManagerID
from #data
where ManagerID = @ManagerID
union all
Select o.EmpID, o.EmpName, o.ManagerName, o.ManagerID
from #data o
join cte c
on o.ManagerID = c.EmpID
and c.EmpID <> @ManagerID)

select * from cte

[Suryakiran]

its working perfect! Thanks!!