how to get the Hierarchy - SQL Server Forums

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

how to get the Hierarchy

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-12 : 02:52:52

how can we get the Hierarchy of the table
it means im my table
i have userid and parentuseriid
when the parentusrid is null that user is top level
when the user having parentusrid that user reporting that parent
how we get the user and that user having reporting user

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 02:56:28

If you are using Microsoft SQL Server 2005, see Common Table Expression in Books Online.

E 12°55'05.63"
N 56°04'39.26"

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-12 : 03:01:24

i can get this by useing with option
i need this with normal query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 03:18:28

Please define "normal query".

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 03:33:11

quote:
Originally posted by boreddy

i can get this by useing with option
i need this with normal query

CTE is part of normal query itself.Refer below and post why you think you cant use it

http://msdn.microsoft.com/en-us/library/ms186243.aspx

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-12 : 04:29:57

Because the CTE table fails when it reaches to get more than 100 record
thats why i am asking here

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-12 : 04:33:37

noremat query means with out useing the CTE table
we may get that busy useing some function?( in case oracle we can get Hierarchy by useing prior by order) like can we get Hierarchy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 04:34:18

If you have more than 100 levels, you have other problems.

Add

OPTION (MAXRECURSION 0)

to the end of your final query that references your cte. Just like Books Online suggests.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 04:39:40

and if you still dont want to use it, use the method in below link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 04:55:07

Using "prior"? Will only work if the two records are adjactent in the resultset, which they may not be.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources