Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

select  
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
share|improve this answer
    
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 '13 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 '13 at 4:56
add comment

Are you looking to create a view like this:

SELECT  t1.employeeid ,
    t1.managerid ,
    t2.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.

share|improve this answer
    
I think I am looking for recursive cte +1 for that –  Biju jose Jan 28 '13 at 5:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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