Author 
Topic 

AskSQLTeam
Ask SQLTeam Question
USA
0 Posts 
Posted  11/29/2000 : 21:11:34

Dan writes "This is a followup comment to article: http://www.4guysfromrolla.com/webtech/sqlguru/q1217991.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 treetraversal 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" 

robvolk
Most Valuable Yak
USA
15732 Posts 

mnorton
Starting Member
7 Posts 
Posted  05/30/2001 : 16:27:59

Dan, I read this chapter in Celko's book and still do not understand the worm going around the tree theory and how I get the numbers for the left and right. What I want to use it for is for a subassembly parts breakdown. Can you help clear up the left and right columns for me since you seem to understand this model?
Thanks, Melanie
quote:
Dan writes "This is a followup comment to article: http://www.4guysfromrolla.com/webtech/sqlguru/q1217991.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 treetraversal 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"




Topic 


