Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Follow up on the recursive join problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/29/2000 :  21:11:34  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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"

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 11/30/2000 :  09:51:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
I found a few web pages with more detail on this:

http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html

These are by Mr. Celko, and man, this guy is a genius.

For some reason, I get redirected when I try to hit www.dbmsmag.com directly, but the format for articles seems to be YYMMd06.html (the lowercase d is literal). I've been stopped after Sept. 1998 (http://www.dbmsmag.com/9809d06.html is the last one).

Go to Top of Page

mnorton
Starting Member

7 Posts

Posted - 05/30/2001 :  16:27:59  Show Profile  Reply with Quote
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/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"




Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000