help with recursive tsql query

Question

Using Sql-Server 2005.

I have Users table with 3 columns. userID, parentUserID and userEmail. Top level users have null in parentUserID, child users have some userID. I want to write a query that counts children up to lowest level of certain userID.

Select user with all his children, their children .... so on. I just need count of those users below top level user. I don't need details or their userID.

thanks

Answer 1

DECLARE @TargetUserId int
SET @TargetUserId = 1;

WITH  Children AS

(
SELECT users.userID, users.parentUserID 
FROM users 
WHERE parentUserID =  @TargetUserId 
UNION ALL
SELECT users.userID, users.parentUserID 
FROM users 
 JOIN Children ON  users.parentUserID = Children.userID
)

SELECT COUNT(*) As SubordinateCount FROM Children