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.
| Author |
Topic |
|
senthil_mca80
Starting Member
10 Posts |
 Posted - 2008-04-04 : 06:21:43
|
| Hi, I need the query for the following condition empId Mgr ID1 02 13 24 25 46 107 38 129 1210 1211 4512 2313 43The above is my table. I want to generate the self join which need to return all the employees applicable for the manager. For exIf I give mgrId=0 the it has to return 0,1,2,3,4,5,7 ( because 0 is manager for 1 and 1is manager for 2, and 2 is manager for 3,4 ...etc)Is there any way to write a query to return this value. Thanks,Senthil |
|
|
Imukai
Starting Member
29 Posts |
Posted - 2008-04-04 : 07:03:56
|
| Check out this article, start about halfway down with the paragraph that begins with Figure 4. This addresses your need:http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-04 : 07:34:32
|
| The 2005 method mentioned in the artical is the way to go. Since this is 2000 forum I will assume that method is not available to you. I don't agree with the (artical above) use of a recursive function. That method doesn't take advantage of sql servers "set based" efficiencies. In other words if there are 10 top level managers and each manager has 10 middle managers and each manager has 10 subordinates then that function would have 1000 iterations. The method below would only have 3 iterations - one for each level.This is an old thread with an example of the setbased approach to hierarchies. There are many others topics on the subject here - some referenced in the thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63042&SearchTerms=hierarchyBe One with the OptimizerTG |
|
|
|
|
|
|
nested inner join