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]
