So referencing this answer: SQL Server CTE Parent Child recursive Here’s a working version with your schema: Table Creation Script CREATE TABLE YOUR_TABLE ([ID] int, [ParentID] int, [Name] varchar(21)) ; INSERT INTO YOUR_TABLE ([ID], [ParentID], [Name]) VALUES (1, NULL, ‘A root’), (2, NULL, ‘Another root’), (3, 1, ‘Child of 1’), (4, 3, ‘Grandchild of 1’), … Read more
Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive: WITH RECURSIVE cte1 AS (…) — can still be non-recursive , cte2 AS (SELECT … UNION … Read more
You’re running into an infinite loop caused by cycles within your data, e.g.: 1 > 2 > 3 > 2 > … . The solution is to keep track of the rows that have already been “consumed”. Due to limitations in CTEs, this has to be done by including the history within each CTE row, … Read more
Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive: WITH RECURSIVE cte1 AS (…) — can still be non-recursive , cte2 AS (SELECT … UNION … Read more