Common expression counters for a table with two unions
If I have a common table expression for a family with a mother and father, how can I increase the "Generation" counter? A family should have a child as generation zero, parents as generation 1, and four grandparents as generation 2. But the cycle is performed twice, one for each grandparent.
;WITH FamilyTree
AS
(
SELECT *, 0 AS Generation
FROM myTable
WHERE [id] = 99
UNION ALL
SELECT name, Generation + 1
FROM myTable AS Fam
INNER JOIN FamilyTree
ON Fam.[id] = FamilyTree.[motherid]
UNION ALL
SELECT name, Generation + 1
FROM myTable AS Fam
INNER JOIN FamilyTree
ON Fam.[id] = FamilyTree.[fatherid]
)
SELECT generation, name FROM FamilyTree
+2
a source to share
1 answer
Change the view of the join to siblings in a break one generation at a time, instead you have 2 recursive clauses in the CTE. 2 sentences form a partial cross join, so you have extra lines
;WITH FamilyTree
AS
(
SELECT *, 0 AS Generation
FROM myTable
WHERE [id] = 99
UNION ALL
SELECT name, Generation + 1
FROM myTable AS Fam
INNER JOIN FamilyTree
ON Fam.[id] IN (FamilyTree.[motherid], FamilyTree.[fatherid])
)
SELECT generation, name FROM FamilyTree
+4
a source to share