Thanks Chris, looks great- I'll give it a whirl ;)
-----Original Message-----
From: Chris Thomas [mailto:[EMAIL PROTECTED]
Sent: 23 January 2004 16:20
To: [EMAIL PROTECTED]
Subject: RE: [ cf-dev ] interesting SQL loop
I use a nested set tree now, but this should do the job - you'll have to
store a depth value - which should make it a little more efficient
anyway. @intItem is the ID of the record to find the crumb trail for.
I've included a safe guard as well to stop it looping to infinity if the
parent/child relationship is broken. I hope it works as I've changed a
few column names from my original SQL.
DECLARE @intDepth INTEGER
DECLARE @intParent INTEGER
DECLARE @intSafeguard INTEGER
SET @intSafeguard = 1
SELECT @intDepth = Depth, @intParent = ParentID
FROM tblItem
WHERE ItemID = @intItem
CREATE TABLE #crumb(ItemID int, Depth INTEGER)
WHILE @intDepth > 1
BEGIN
IF @intSafeguard > 20
BEGIN
GOTO BREAKPOINT
END
INSERT INTO #crumb(ItemID, Depth)
VALUES (@intItem,@intDepth)
SELECT @intDepth = Depth, @intParent = ParentID,
@intItem = ItemID
FROM tblItem
WHERE ItemID = @intParent
SET @[EMAIL PROTECTED]
END
BREAKPOINT:
SELECT *
FROM #crumb
ORDER BY Depth
DROP TABLE #crumb
-----Original Message-----
From: Damian Watson [mailto:[EMAIL PROTECTED]
Sent: 23 January 2004 15:40
To: [EMAIL PROTECTED]
Subject: [ cf-dev ] interesting SQL loop
Hi,
Any ideas would be appreciated:
I've got db content with an ID and a parentID. parentID can be null
denoting a top of the tree piece of content. So I could have a content
tree like:
ID: 1 ParentID: NULL
>ID: 2 ParentID: 1
>ID: 3 ParentID: 2
Potentially ad infinitum.
The question is how would I create a query that could select the IDs for
this lineage if I am starting with the lowest rung ID of 3.
In short I'm trying to create a crumbtrail dynamically and I don't want
to limit it to three rungs...
Cheers
d
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
