CODEWITHSUNDEEP
sqlsql-server-2005t-sql
eugeneK
eugeneK

Reputation: 11146

help with recursive tsql query

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

Upvotes: 2

Views: 172

Answers (1)

Martin Smith
Martin Smith

Reputation: 453887

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

Upvotes: 3

Related Questions