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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Follow up on the recursive join problem

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-29 : 21:11:34
Dan writes "This is a followup comment to article: http://www.4guysfromrolla.com/webtech/sqlguru/q121799-1.shtml

The solution that I have chose to use for this problem would be familiar to a CS major playing with trees using a modified preorder tree-traversal algorithm.

I believe you were on the write track with a Root Level number, but this really limits you from doing a lot of useful queries. Like returning all the children of a specific parent, for example.

A Visitation Representation of Trees needs 2 extra fields, LEFT and RIGHT. It can be imagined as if a little worm crawling through your tree starts at the root, and makes a complete trip around the tree. When he comes to a node, he puts a number in the side of the cell he's visiting (LEFT or RIGHT), and increments his counter. What you get here is not just the level of the tree, but how many children are underneath you.

Once you have this structure, you can do about any kind of query and get what you want with a little imagination. But when you add/delete from the tree, you also need to enumerate the whole thing again, or better yet, use a set of sql statements to just update the tree.

Ok, so this wasn't my idea, I read it in Joe Celko's "SQL for Smarties," but it is VERY relevant to this topic, so I gave a short summary of his chapter. All credit and thanks goes to Mr. Celko. And he does include all those cool functions you will need. Not sure if this stuff is available on the web anywhere or not.

Until TSQL gets a recursive join function, this is the best solution I found.

-DH"
   

- Advertisement -