question

Anant-7621 avatar image
0 Votes"
Anant-7621 asked MelissaMa-msft commented

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

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

















sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Anant-7621,

Could you please validate the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @Anant-7621,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.