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?
A:
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
Quassnoi
2010-02-03 12:42:06
@KM: use scroll down :)
Quassnoi
2010-02-03 12:56:24
duh!!!!!!!!!!!!!!!
KM
2010-02-03 13:21:55