4Guys article - SQL Server Forums

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)

4Guys article

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-31 : 18:50:49

Alain Rostain writes "Hi. In the above response, I think you misunderstood what the Michael was asking, or at least I don't think what you provided answers the question. Michael was looking (as am I) to return an entire tree starting at a root (or in my case, a leaf). I believe this can only be done directly using connect by which is not available until SQL Server 2005.

I think that your solution only returns one level down a thread. So if 1 is the root, and 2's parent is 1, and 3's parent is 2, it would only return 1 & 2 if you started with 1. Am I missing something here?

Thanks. --- Alain"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-10-31 : 18:50:49

Alain,

I'd search on this site for articles on trees and hierarchys. YOu'll find more recent code.

-Bill

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-31 : 19:42:49

See this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25964

for some simple ideas. Recursive SELECT's in T-SQL are easily implemented as UDF's; you will need to loop through the hiearchy once per depth of the tree, but since that is a logarithmic operation (generally) it is a very small price to pay and very easy to implement.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 00:40:43

Refer these also

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-02 : 17:18:34

I've found the use of an accumulater table to be simpler to code and more efficient than any recursive algorithm, and is also not subject to limits on recursion levels.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-02 : 22:16:56

quote:
Originally posted by blindman

I've found the use of an accumulater table to be simpler to code and more efficient than any recursive algorithm, and is also not subject to limits on recursion levels.

Do you have an example?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-03 : 09:46:23

Skeleton code for finding child records in a recursive relationship:

--This variable will hold the parent record ID who's children we want to find.
declare @RecordID int
set @RecordID = 13

--This table will accumulate our output set.
declare @RecordList table (RecordID int)

--Seed the table with the @RecordID value, assuming it exists in the database.
insert into @RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @RecordID

--Add new child records until exhausted.
while @@RowCount > 0
insert into @RecordList (RecordID)
select YourTable.RecordID
from YourTable
    inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID =  YourTable.RecordID)

--Return the result set
select RecordID
from @RecordList

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-03 : 09:58:14

Yeah, that's almost exactly the same as my UDF. I used the word "recursive", but it's not really. It's iterative. YOu should look at the UDF code I've posted, it more efficient since it doesn't pull in every possible child starting with the original root each time before filtering it out as your example code does; it only pulls in the children of the previous level in the tree. Also, it returns the relative level of each child so you can see how far down from the starting point it is. (I know I've improved upon it since and made it a little shorter, but that first post is the only one I could track down)

In general, I also have found that leaving your data modelled in a standard parent/child manner, and then querying it this way, is much easier and more efficient than using all of those "tricks" store hiearchies in alternate manners (i.e., the "nested set model" or storing node paths as strings or using helper tables).

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-03 : 10:03:14

quote:
Originally posted by jsmith8858
...
In general, I also have found that leaving your data modelled in a standard parent/child manner, and then querying it this way, is much easier and more efficient than using all of those "tricks" store hiearchies in alternate manners (i.e., the "nested set model" or storing node paths as strings or using helper tables).

I think I would partially disagree. The information should be stored in a standard parent child manner, but generating a 'paths' table allows much more information about the tree to be queried very simply.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-03 : 10:10:22

The problem is *maintaining* those path tables for the various operations you need to perform on the tree (creating, moving, adding, deleting nodes), and keeping them update to date and accurate.

It depends on the needs of what you are storing.

If it is somewhat small but changes often, store in normally and use a simple UDF like the one I've shown. If it is really big, and almost never changes, then storing additional info like paths to allow for quick SELECTs definitely could be worth it.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-03 : 10:12:34

nah... i just rebuild if a relationships is added...

it rebuilds in a second or two... in some cases i build it just to do the query...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-03 : 10:20:29

quote:
Originally posted by Seventhnight

nah... i just rebuild if a relationships is added...

it rebuilds in a second or two... in some cases i build it just to do the query...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Exactly! You've made my point.

Re-building your "paths" table to facilitate your SELECT is exactly what the UDF's I've posted and also blindman's method does, only our "path" tables are table variables. In addition, when you rebuild a permanent table in your DB for SELECT's, then you have concurrency issues.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-03 : 11:19:21

no.. not true.. i only re-build if something changes (which is not that often)

and in one experimental case, i was building the tree on demand, but that was mostly for display purposes. One sortable recordset for a sectional 'tree view'

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-03 : 11:21:13

P.S. how does your method allow for sorting of the children, or display in tree view fashion?

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-03 : 11:37:33

Not sure what you mean .... sorting of children? You can use any ORDER BY. For displaying, that's a presentation layer issue of course.

Any good examples?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-03 : 13:06:14

Displaying in a tree view can be easily handled by using the Level value in conjunction with the REPLICATE() function.
IF you really want to do this outside of your presentation layer...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-03 : 13:33:54

quote:
Originally posted by blindman

Displaying in a tree view can be easily handled by using the Level value in conjunction with the REPLICATE() function.
IF you really want to do this outside of your presentation layer...

How would you sort the following without a ton of work client side?


--Groups
--___Cars
--______Ford
--______Lexus
--___Fruits
--______Apple
--______Banana


Create Table #Items (Id int identity(1,1), label varchar(100))
Insert Into #Items Select 'Banana'
Insert Into #Items Select 'Lexus'
Insert Into #Items Select 'Cars'
Insert Into #Items Select 'Fruits'
Insert Into #Items Select 'Apple'
Insert Into #Items Select 'Ford'
Insert Into #Items Select 'Groups'

Create Table #Tree (parentId int, ChildId int, Level int)
Insert Into #Tree Select 7, 3, 1
Insert Into #Tree Select 3, 2, 2
Insert Into #Tree Select 3, 6, 2
Insert Into #Tree Select 7, 4, 1
Insert Into #Tree Select 4, 1, 2
Insert Into #Tree Select 4, 5, 2

If you have a path table, then it just works. You can query the for paths that start like 'something', end like 'something', contain 'something'. You can easily count the number of nodes below a given node... blah blah blah

I'll hunt down my articles again... stupid BFE...(can't get cable or DSL)

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources