How to link each column in sql?

Question

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.

Answer 1

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

Answer 2

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.