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 |
|
overthetop
Starting Member
18 Posts |
 Posted - 2006-06-01 : 05:34:25
|
| HiI have a table (Id, Name, ManagerId)Is it posible to return all the names in hierarchy order using a select statment? I hope someone can write down the query :)10x |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
overthetop
Starting Member
18 Posts |
Posted - 2006-06-01 : 05:44:17
|
yes but this uses temp table. I mean only with select statment without dynamic sql. Something like thisselect Id, Namefrom .... join ...... join ...I really prefer using CTE but the reason I post this question is that today in the university gave us a task to do it and I think that the professor was joking  |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-01 : 06:04:02
|
Is it unlimited no of levels or if it is limit to few you can use inner join to join back the same table
KH |
|
|
|
overthetop
Starting Member
18 Posts |
Posted - 2006-06-01 : 06:06:39
|
The number of levels is unlimited. That is the problem  |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-01 : 06:11:36
|
Yes it is really a long problem 
KH |
|
|
|
overthetop
Starting Member
18 Posts |
Posted - 2006-06-01 : 06:12:51
|
| xex |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-01 : 07:40:14
|
quote:
Originally posted by overthetop
yes but this uses temp table. I mean only with select statment without dynamic sql. Something like thisselect Id, Namefrom .... join ...... join ...I really prefer using CTE but the reason I post this question is that today in the university gave us a task to do it and I think that the professor was joking
Read Nigel's approach. There is no dynamic SQL. You cant avoid having temp table if the level is unlimitedMadhivananFailing to plan is Planning to fail |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2006-06-01 : 08:29:23
|
| If you're using SQL 2005 use a CTE |
|
|
|
|
|
|
Recursion only with select