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