Total number of children based on parenting
I have 2 tables: table1
.table2
Parent Child Point Parent Total
a b 100 a 0(default) (result = 1050)
b c 200 b 0 (result = 950)
c d 250 c 0 (result = 750)
d e 500
The result in table2
must be the sum of the child points based on the parent in table1
.
a---b---c---d---e
I tried many times but couldn't figure it out.
UPDATE table2 set Total=???
0
source to share
2 answers
Use a recursive CTE :
WITH RECURSIVE cte AS (
SELECT parent, child, point AS total
FROM tbl1
UNION ALL
SELECT c.parent, t.child, c.total + t.point
FROM cte c
JOIN tbl1 t ON t.parent = c.child
)
SELECT *
FROM cte
+2
source to share
It hurts my brain ... The following should work for you, please note that this is very crude and you will want to simplify it.
DECLARE @parent NCHAR(1), @child NCHAR(1), @runningTotal INT
SET @parent = 'a' -- set starting parent here
DECLARE myCursor CURSOR FOR SELECT [Parent], [Child], [Point] FROM table1 WHERE [Parent] = @parent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @parent, @child, @runningTotal
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM table1 WHERE [Parent] = @child)
BEGIN
DECLARE @point INT
SELECT @parent = [Parent], @child = [Child], @point = [Point] FROM table1 WHERE [Parent] = @child
SET @runningTotal = @runningTotal + @point
END
ELSE
BEGIN
BREAK
END
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT @runningTotal
0
source to share