recursion on table ID - 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)

recursion on table ID

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-24 : 21:57:00

Hi,

My table:

Table Blah
(
blahID INT,
ParentBlahID INT,
Name VARCHAR(50)
)

Can someone help me with a recursive function that I can do at the Sql Server level that can give me a breadcrumbs string back?

e.g hello/world/thanks

Is this possible? If so, is it really bad in terms of performance? (maximum realistic levels are going to be mabye 5-7).

Thanks!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-24 : 22:03:22

Sounds like exactly what this does http://www.sqlteam.com/item.asp?ItemID=8866
In fact, it even stores the "breadcrumbs"

Damian

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-27 : 23:01:30

how to you get the parent node of any given node?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-28 : 12:19:35

In the article, the parent node is stored with the node, and the lineage/breadcrumbs are built from these values.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-30 : 06:26:51

Sorry what I meant was the TopLevel Parent of any given node.

I guess I have to do some string hacks to parse the breadcrumbs to get the node?

Example: '/100/101/102/'

I have to get the index of the 1st and 2nd '/' and then grab the 100 and that's teh ID of the TOp Level parent?

And if there is only a '/' we are at the toplevel...correct?

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-30 : 06:28:53

Question#2

Is there a query that can update the # of content items linked to each category?

Category#1 (25)
SubCategory#2 (10)
SubCategory#3 (15)
SubSubCategory#3.1 (10)
SubSubCategory#3.2 (5)

Is that possible? Realy stumped on this!

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-06-18 : 07:25:23

anyone? Still haven't found the solution to find the # of subcategories under a category. I want a batch query to do this, not on the fly.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-18 : 07:28:10

try this:

http://www.seventhnight.com/treeStructs.asp

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-06-18 : 08:40:19

I'm looking for the solution from this architecture: http://www.sqlteam.com/item.asp?ItemID=8866

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-18 : 09:07:42

Maybe something like this??

Select
Node,
Cnt = (Select sum((Select count(*) From contents where Node = Z.Node)) From Tree as Z Where Z.lineage like A.lineage + A.Node + '/')
From Tree as A

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources