Walter,
This problem is actually easy to solve in SQL Server 2000. SQL Server 2000 now has UDF's which can be recursive.
Here's a UDF from
www.foxforum.com that retrieves a message's depth (how low in the tree is the message)
ALTER function forum_get_message_depth
(@messageid as int, @intCurrentDepth as int)
Returns int
as
BEGIN
DECLARE @intParentID INT
SELECT @intParentID = parentid
FROM messages where messageid = @messageid
DECLARE @intRetVal INT
SET @intRetVal = 0
SET @intCurrentDepth = @intCurrentDepth + 1
If @intParentID = 0
SET @intRetVal = @intCurrentDepth
Else
BEGIN
SET @intRetVal = dbo.forum_get_message_depth(@intParentID, @intCurrentDepth)
END
Return @intRetVal
END
Rodman
Rod Paddock
Editor in Chief CoDe Magazine
President Dash Point Software, Inc.
VP Red Matrix Technologies,Inc.