Hierarchical data - SQL CTE counting childs recursion

Questions

I d like (using cte) to count children in table in that way to have at parent level number of all children including theirs children . Is there any sample available?

Answers

CREATE TABLE t_parent (id INT NOT NULL PRIMARY KEY, parentID INT NOT NULL)

INSERT
INTO    t_parent
VALUES  (1, 0)
INSERT
INTO    t_parent
VALUES  (2, 1)
INSERT
INTO    t_parent
VALUES  (3, 1)
INSERT
INTO    t_parent
VALUES  (4, 2)
INSERT
INTO    t_parent
VALUES  (5, 1)
INSERT
INTO    t_parent
VALUES  (6, 5)
INSERT
INTO    t_parent
VALUES  (7, 5);

WITH    q AS
        (
        SELECT  id, parentId
        FROM    t_parent
        UNION ALL
        SELECT  p.id, p.parentID
        FROM    q
        JOIN    t_parent p
        ON      p.id = q.parentID
        )
SELECT  id, COUNT(*)
FROM    q
GROUP BY
        id

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/2191847/sql-cte-counting-childs-recursion

Related