Menu
I have a recursive CTE query, but it fails when a loop is created. I already fixed simple loops (e.g. 1 -> 2 -> 1), but cannot fix more complex loops (e.g. 1 -> 2 -> 3 -> 2).
The test table has two columns: Base and Parent. I want a list with all ancestors.
My query works on the sample data below if you start at test2, but not when you start at test1.
Sample Data
Base Parent
---- ------
test1 test2
test2 test3
test3 test2
SQL Query (my attempted fix is marked in comments)
;with sample_data (Base, Parent) as (
select 'test1', 'test2'
union select 'test2', 'test3'
union select 'test3', 'test2'
),
nt_list (Base, Ancestor, [level]) as (
select Base,
Parent Ancestor,
1 [level]
from sample_data
where Base = 'test1' -- START HERE
union all
select ntl.Base,
nt.Parent,
ntl.[level] + 1 [level]
from nt_list ntl
join sample_data nt on ntl.Ancestor = nt.Base
where nt.Parent <> ntl.Base -- fix recursive bug (e.g. 1 -> 2 -> 1)
-- WHAT I TRIED TO ADD BUT CANNOT: (e.g. 1 -> 2 -> 3 -> 2)
and nt.Parent in (select Ancestor from nt_list)
)
select distinct
ntl.Base,
ntl.Ancestor
from nt_list ntl
order by Ancestor
SQL Error: Recursive member of a common table expression 'nt_list' has multiple recursive references.
221
A CTE can be recursive or non-recursive. A recursive CTE is a CTE that references itself. A recursive CTE can join a table to itself as many times as necessary to process hierarchical data in the table. CTEs increase modularity and simplify maintenance.
We can change the CTEs default maximum recursion by specifying the MAXRECURSION query hint. Change the previous recursive CTE to generate numbers between 1 to 200 by specifying the MAXRECURSION hint value as 210 as below and verify the result: ?
A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. FROM cte_name; Again, at the beginning of your CTE is the WITH clause.
One of the most useful benefits of the CTE is the ability to create a recursive query within it. This means that the CTE can be self-referenced within the same query. But if it is not designed carefully, it may result in an infinite loop. To prevent that, SQL Server set the default value of the recursion level to 100.
The final version. Assuming '/' will never be part of the base or parent name.
;with sample_data (Base, Parent) as (
-- TEST 1
-- select 'test1', 'test2'
--union select 'test2', 'test3'
--union select 'test3', 'test2'
-- TEST 2
select 'test1', 'test2'
union select 'test2', 'test3'
union select 'test3', 'test4'
union select 'test3', 'test9'
union select 'test4', 'test5'
union select 'test5', 'test3'
union select 'test9', 'test8'
-- TEST 3
-- select 'test1', 'test2'
--union select 'test2', 'test3'
--union select 'test3', 'test1'
-- TEST 4
-- select 'test1', 'test1'
--union select 'test1', 'test2'
),
nt_list (Base, Ancestor, [level], [path]) as (
select Base,
Parent Ancestor,
1 [level],
'/' + convert(varchar(max), rtrim(Base)) + '/' [path]
from sample_data
where Base = 'test1' -- START HERE
union all
select ntl.Base,
nt.Parent,
ntl.[level] + 1 [level],
ntl.[path] + rtrim(nt.Base) + '/'
from nt_list ntl
join sample_data nt on ntl.Ancestor = nt.Base
where ntl.path not like '%/' + rtrim(nt.Parent) + '/%'
)
select distinct
ntl.Base,
ntl.Ancestor
from nt_list ntl
order by Ancestor
You can use
;WITH nt_list (Base, Ancestor, [level], cycle, path)
AS (SELECT Base,
Parent Ancestor,
1 [level],
0 AS cycle,
CAST('.' AS VARCHAR(max)) + ISNULL(Parent, '') + '.' + Base + '.' AS [path]
FROM NoteTest
WHERE Base = 'test1'
UNION ALL
SELECT ntl.Base,
nt.Parent,
ntl.[level] + 1 [level],
CASE
WHEN ntl.[path] LIKE '%.' + LTRIM(nt.Base) + '.%' THEN 1
ELSE 0
END AS cycle,
ntl.[path] + LTRIM(nt.Base) + '.' AS [path]
FROM nt_list ntl
JOIN NoteTest nt
ON ntl.Ancestor = nt.Base
AND ntl.cycle = 0)
SELECT ntl.Base,
ntl.Ancestor
FROM nt_list ntl
ORDER BY Ancestor
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
