Query: Parent / Child reference in same table

[BuGlen]

Good Afternoon,

I'm trying to find a query that will return a result set from a table that has the parent / child relationship in the same table. The table contains articles of information for a knowledge base application which is structured around a tree type interface. Each of the records contain a NodeId field (primary index) and a ParentId field that ties it to the related parent node.

So, what I need to do is be able to specify the NodeId and return a result set that contains that article, and all of the children, grandchildren, great-grandchildren, etc... Currently I'm doing it programatically by looping through each level of the children and requerying based on the NodeIds returned to get the next level. I'm hoping to simplify this with a query based solution if possible.

Any ideas would be greatly appreciated.

- Glen

Know thy data.

 

[SemperFiDownUnda]

Whay you have is a recursive table. To pull out the hierarchical result set. This can not be done via a normal query.

You have to do this programatically.
I normally do this with SP that I recursively call.
The first pass looks up all the child nodes for the given record.
This result set is then passed back into the sp and all the children for that result set is found. etc etc etc until the result set comes back empty (meaning you've hit the bottom of the barrel.

Not very useful.

Another thing you could do is create a UDF that returns true or false if a record is a offspring of another record by traversing up the the tree and just to a table scan based on that.

If someone has a better method I'd love to hear it.

These days I tend to use controls, where ever possible, that lets the user drill down and I just grab the children of the node they are drilling into.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[BuGlen]

Hi Wayne,

That's been the general concensous of the posts I've been reading in Google Groups as well. It looks like a simple query can not accomplish this.

For normal data view, a treeview control is used and it works quite well to browse the hierarchical data. The reason I need a full branch of the tree is for printing functions and expandable XML data functionality, and I was hoping there was a more efficient way to retrieve the data.

From what I've read, Oracle server will do this using a connect statement. Maybe the next version of MSSQL will incorporate this type of functionality as well.

Thank you for the reply in any case.

- Glen

Know thy data.