Query down the tree 2

[mbowles]

I have a table with an ID(autonumber), parent_id, name, etc... fields.
It is a one to many relationship with itself. I.E. each rows parent_id points to the ID of another row. (with one exception, the first records parent_id = -1 to designate the root of the tree).

How can I query down and interact with all children of a single parent. I.E. I have ID = 6. I want to find all rows with parent_id = 6 and all rows beneath them and so on until I run out of rows?

 

[vongrunt]

So basically you want to extract a subtree starting from node X ... and maximum depth is not limited?

This is the most common approach to solve this problem (using extra varchar column for "indexing")

http://www.sqlteam.com/item.asp?ItemID=8866

This one is my favorite, a bit more hardcore but very elegant:

http://www.intelligententerprise.com/001020/celko.jhtml

 

[ESquared]

T-SQL to establish hierarchy

Versatile High Performance Hierarchies in SQL Server

Trees in SQL: Nested Sets and Materialized Path (highly technical)


-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ESquared]

faq183-5322

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[mbowles]

thanks, I found all of the links provided to be very useful. I ended up using the books online version for now, as my "tree" will rarely have deeper than 5-6 levels for a year or more. It runs really well. My problem was that I am not a SQL guyu so was not aware of the term "adjacent heirarchy" Thanks a ton and a learned quite a but form all of the links.