Let's say I have a table with 3 columns:
- item ID 'ID'
- parent ID 'ParentID'
- item name 'Title'
Now, how should I count how many children a Root has?
Asked
Active
Viewed 515 times
0
-
Thanks Mat for the correction :) – M. Parsa Oct 01 '11 at 07:29
-
If you want to count descendants, this is your answer http://stackoverflow.com/questions/2340696/counting-number-of-children-in-hierarchical-sql-data – evpo Oct 01 '11 at 07:31
-
You could see this answer: http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server/959821#959821 – Benoit Oct 01 '11 at 07:33
-
@Benoit - Or the OP could just see the answer on this page! – Martin Smith Oct 01 '11 at 07:36
1 Answers
3
SELECT COUNT(*)
FROM T
WHERE ParentID = @ParentID
If you want descendants not just immediate children you would need a recursive CTE.
;WITH R AS
(
SELECT ID
FROM T
WHERE ParentID = @RootID
UNION ALL
SELECT T.ID
FROM T
JOIN R ON R.ID = T.ParentID
)
SELECT COUNT(*)
FROM R
Martin Smith
- 438,706
- 87
- 741
- 845
-
Yes Martin, that's exactly what I'm looking for. I need to count all the children of children.. – M. Parsa Oct 01 '11 at 07:30
-
-
-