Parent and grand parent and great grand parent etc.

Hi,
I have a table with id, parent-id.
I want to know how to write a stored procedure which returns the ids of
parent, grand-parent, great-grand-parent etc.
So, if the structure is like this
id Parent-id
Tree Control ASP.NET Controls
ASP.NET Controls ASP.NET
ASP.NET .NET
.NET VS
VS MS
Office MS
A parent can have more then one child
If given 'Tree Control' This stored procedure returns a record set like
following:
Tree Control
ASP.NET Controls
ASP.NET
.NET
VS
MS
ThanksOthers may have a better solution for this, but I worked out the
following for a previous question:
http://groups.google.com/group/SQL-...94663b43a00f67d
Might give you some insight.
HTH,
Stu|||Get a copy of TREES & HIERATRCHIES IN SQL for better ways to do this.|||I am looking for a simple query (at least for you guru guys out there).
For a given node, it should return the list of all the parents of it's
parent/grand parent. Usually people have examples for children - I am
not looking for children, I am looking for parent.
thanks|||Have a look at
http://toponewithties.blogspot.com/...er.htm
l
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123511813.304233.160130@.o13g2000cwo.googlegroups.com...
>I am looking for a simple query (at least for you guru guys out there).
>
> For a given node, it should return the list of all the parents of it's
> parent/grand parent. Usually people have examples for children - I am
> not looking for children, I am looking for parent.
> thanks
>|||Sorry, anything else? I don't want to get into prime number.
I am just looking for a simple query/stored procedure.
thanks|||OK, What about this one.
http://www.windowsitpro.com/SQLServ...es.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123522959.424109.230440@.g49g2000cwa.googlegroups.com...
> Sorry, anything else? I don't want to get into prime number.
> I am just looking for a simple query/stored procedure.
> thanks
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Good design is crucial in these cases - you must never allow for circular
references.
ML