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 |
|
zafarthesultan
Starting Member
16 Posts |
 Posted - 2009-07-08 : 10:05:04
|
| Hello folks,I am new to sql server and need some help. I have the "same old family tree creating problem". A | / \ B C / \ / D E I have parent,leftnode and rightnode in my table.What I need is 1)number of left nodes of a particular parent2)number of right nodes of a particular parent3)total number of nodes of a particular parent4)number of levels completed (in above tree A has level 1 completed)Guys, I am not an SQL expert and has tried many forums but I have not succeeded yet.Although I have tried CTEs but as far as I know it exhausts after 100 iterations.So what can I do to achieve the solution? Please help. TIA. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-08 : 11:04:07
|
AddOPTION (MAXRECURSION 0) to the query that consumes the cte.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
zafarthesultan
Starting Member
16 Posts |
Posted - 2009-07-08 : 12:25:18
|
| OK Thats fine. Thanks. But how to check the levels completed? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 12:32:47
|
quote:
Originally posted by zafarthesultan
OK Thats fine. Thanks. But how to check the levels completed?
which levels? it will automatically get completed. you dont need to specifically do check.However if you want to check, just add a derived column to resultset which is set to 1 and incremented by 1 in recursive part. then check max(intcol) to get levels traversed. |
|
|
|
|
|
|
Family Tree