hierarchy related query...how do i pass the result of the query to another query as where clause values....

Question

Hi All,
Thanks a lot for your time, i have this set of data in a table.. i am trying to derive the additional columns so that each field has a level value that can be used to filter

Employee_ID Manager_ID Title
A101 null CEO
A102 A101 CFO
A103 A101 CIO
A104 A101 CTO
A201 A102 Director
A202 A102 Director
A301 A103 Director
A302 A103 Director
A401 A104 Director
A402 A104 Director
A501 A201 Vice President
A502 A201 Vice President
A503 A202 Vice President
A504 A202 Vice President
A504 A301 Vice President
A505 A302 Vice President
A506 A401 Vice President
A507 A401 Vice President
A508 A402 Vice President
A509 A402 Vice President
A601 A501 AVP
A602 A501 AVP
A603 A502 AVP
A604 A502 AVP
A605 A503 AVP
A606 A503 AVP
A608 A504 AVP
A609 A504 AVP
A610 A505 AVP
A611 A505 AVP
A612 A506 AVP
A613 A506 AVP
A614 A507 AVP
A615 A507 AVP
A616 A508 AVP
A617 A508 AVP
A618 A509 AVP
A619 A509 AVP
A701 A601 Associate
A702 A601 Associate
A703 A601 Associate
A704 A601 Associate
A705 A602 Associate
A706 A602 Associate
A707 A602 Associate
A708 A602 Associate
A709 A602 Associate
A710 A603 Associate
A711 A603 Associate
A712 A603 Associate
A713 A603 Associate
A714 A603 Associate

Employee_ID Manager_ID Level1(CEO) Level2(CFO) Level3(CIO) Level4(CTO) Level5(Director) Level6(Vice President) Level7(AVP)
A701 A601 A101 A102 A103 A104 A201 A501 A601

Query1 is for CEO so that i get the Level 1 column (first column)
select distinct Employee_ID
where Employee_ID=A101

Level1
A101

query2 - This query is to get the Level 2 - Level is direct reports of CEO
select distinct Employee_ID
where Manager_ID= A101 --null is for CEO; i know the ID of CEO

This query will give me the list of unique manager IDs under CEO - Level 2 column
A102
A103
A104

query3 - this query is to get the list of all employeeIDs where the managerIDs are from the above query (A102, A103,A104)
Select Employee_ID
where Manager_ID in A102, A103,A104

output

A201
A202
A301
A302
A401
A402

could you please suggest; i know the Employee_ID of the CEO to get the Level 1 ; which gives me the list of Level 2 then i need to get the list of employee_Ids
using the output .....repeat this until i get all the levels - my number of levels is fixed and i know this is not going to change - level 6 or 7

any suggestions please

Answer

Hi @Anant ,

Welcome to Microsoft Q&A!

You could have a try with Recursive CTE.

Please refer to below and check whether it is helpful.

;with HierarchyLvl as  
(  
SELECT [Employee_ID]  
,[Manager_ID]  
,[Title]  
,1 as Level  
FROM [dbo].yourtable  
where [Manager_ID] is null  
UNION ALL  
SELECT e.[Employee_ID]  
,e.[Manager_ID]  
,e.[Title]  
,Level + 1  
FROM [dbo].yourtable e INNER JOIN HierarchyLvl d on e.[Manager_ID] = d.[Employee_ID]  
)  
select * into #tempHierarchyLvl  
from HierarchyLvl

Query1:

select [Employee_ID] from #tempHierarchyLvl  
where level=1

Output:

Employee_ID  
A101

Query2

select [Employee_ID] from #tempHierarchyLvl  
where level=2

Output:

Employee_ID  
A102  
A103  
A104

Query3

select [Employee_ID] from #tempHierarchyLvl  
where Manager_ID in (  
select [Employee_ID] from #tempHierarchyLvl  
where level=2)

Output:

Employee_ID  
A401  
A402  
A301  
A302  
A201  
A202

Or you could combine them into one row like below:

select a.Employee_ID,a.Manager_ID,f.Employee_ID level1,e.Employee_ID level2,d.Employee_ID level3,c.Employee_ID level4,b.Employee_ID level5  
from yourtable a  
left join yourtable b on  a.Manager_ID=b.Employee_ID  
left join yourtable c on  b.Manager_ID=c.Employee_ID  
left join yourtable d on  c.Manager_ID=d.Employee_ID  
left join yourtable e on  d.Manager_ID=e.Employee_ID  
left join yourtable f on  e.Manager_ID=f.Employee_ID  
where a.Employee_ID='A701'

Output:

Employee_ID Manager_ID level1 level2 level3 level4 level5  
A701 A601 A101 A102 A201 A501 A601

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Please see:
https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child

The CEO has a ManagerId = NULL

hierarchy related query...how do i pass the result of the query to another query as where clause values....

output

2 answers