Rod,

Too bad you spoiled the game, I wanted JVP to come up with this.

However, there is something to say about such solution:

1. It is not SQL - ANSI 92 compliant.
2. If you don't use SQL 2000 or up, you're screwed.
3. It still is using a SQL SELECT statement for each and every node. Since the nature of a SQL command and its seperation from the interal database scheme (indexes) it is likely to be far slower that the SEEK() solution in VFP.

Second, how about the second part of the challenge ? No, please don't answer, I'd like to see if JVP can come up with the solution.

Walter,




>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