I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase my knowledge in T-sql. The current scenario was simple as to do a self join. the schema for this scenario is given below

create table resource(
res_id int,res_name varchar(20), man_id int)

populating the content and running the query below give the resource name and its manager

SELECT a.res_name,b.res_name AS manager FROM dbo.RESOURCE a INNER JOIN dbo.RESOURCE b 
ON b.res_id=a.man_id

res_name   manager
sam       will
sunny     helen
will      micah

but next time my friend entered into my cubicle he need a different answer from the above he needs a result like this

sam will micah

so how will I chain the results I no idea where to start.

2 Answers 2


It can also be nice to do it the other way up - starting from the top level management working down through the organizational heirarchy, as here. As a guide if an organisation has around 8 staff per manager then 5 levels of management will be enough for up to 8^5=32768 staff so you will rarely need recursion for this.

level1.res_name as level1,
level2.res_name as level2,
level3.res_name as level3,
level4.res_name as level4,
level5.res_name as level5

from resource as level1
left join resource as level2 on level1.res_id=level2.man_id
left join resource as level3 on level2.res_id=level3.man_id
left join resource as level4 on level3.res_id=level4.man_id
left join resource as level5 on level4.res_id=level5.man_id

where isnull(level1.man_id,0)=0
  • that's fantastic. a very good implementation without CTE. I really loved it. I thought it won't be possible to create a hierarchy without using CTE again thanks chris
    – Biju jose
    Jan 30, 2013 at 3:03
  • can you do me a help, the query above is great but I looked inside it but I didn't got the inside working of it. can you please suggest some wise advice to me (for building the basics ,some web tutorials will be fine) to solve the problems arising in hierarchy
    – Biju jose
    Jan 30, 2013 at 4:56

Are you looking to create a view like this:

SELECT  t1.employeeid ,
    t1.managerid ,
FROM    emp t1
    INNER JOIN emp t2 ON t1.managerid = t2.employeeid

If you want to get the employee hirarchy you should use recursive CTE. Check this article at SqlAuthority: SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE.

  • I think I am looking for recursive cte +1 for that
    – Biju jose
    Jan 28, 2013 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.