Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need a query to form the below logic

Author  Topic 

btechrames
Starting Member

3 Posts

Posted - 2014-06-08 : 10:50:07
hi friends,

I need to write a sql that generate the hierarchy in an organization.
Below an example
emplid empname supervisor_id superv_name
1 subu null null
2 vid 1 sub
3 ram 4 satis
4 satis 2 vid

i need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.

whoever joinng new to org the h

empid empname supervisor_id superv_name
3 ram 4 satis
4 satis 2 vid
2 vid 1 subu
4 satis 1 subu
3 ram 1 subu
3 ram 2 vid
5 kumar 1 subu
5 kumar 4 satis
5 kumar 2 vid
1 subu null null


Thanks n advance...

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-08 : 15:21:02
Something like this perhaps:
with cte
as (select *
from yourtable
union all
select a.emplid
,a.empname
,b.supervisor_id
,b.superv_name
from cte as a
inner join yourtable as b
on b.emplid=a.supervisor_id
where a.supervisor_id is not null
and b.supervisor_id is not null
)
select *
from cte
Go to Top of Page

btechrames
Starting Member

3 Posts

Posted - 2014-06-10 : 00:53:01
Hi Friends..Is any other method without using cte?please
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-10 : 08:48:24
bitsmed's method uses a recursive cte. That's a nice, succinct approach. If you don't want that, use a series of left joins. The only hitch is you need to know in advance how many levels the organization has. Basically you need a left join per level (+|1-)

something like:


select * from yourtable level0
left join (select empname from yourtable) level1
on level0.supervisor_id = level1.emplid
left join (select empname from yourtable) level2
on level1.supervisor_id = level2.emplid
...etc...


btw, sometimes recursive ctes are bad performers. They sometimes generate O(n^m) execution plans, where n is the number of rows in your base table, and m is the recursion depth needed to get the job done. You have to look at the plans and measure the performance. However, they are nice and simple to write!
Go to Top of Page
   

- Advertisement -