SQLTeam.com | Weblogs | Forums

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

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:

Annotation 2020-07-23 123549

Thanks in advance for your help.

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

image

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

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

image

;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;

image

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

its working perfect! Thanks!!