common table expression - SQL Recursive CTE: preventing a recursive loop by multiple recursive references -

common table expression - SQL Recursive CTE: preventing a recursive loop by multiple recursive references -

question

i have recursive cte query, fails when loop created. fixed simple loops (e.g. 1 -> 2 -> 1), cannot prepare more complex loops (e.g. 1 -> 2 -> 3 -> 2).

query detail

the test table has 2 columns: base of operations , parent. want list ancestors.

my query works on sample info below if start @ test2, not when start @ test1.

sample data

base parent ---- ------ test1 test2 test2 test3 test3 test2

sql query (my attempted prepare marked in comments)

;with sample_data (base, parent) ( select 'test1', 'test2' union select 'test2', 'test3' union select 'test3', 'test2' ), nt_list (base, ancestor, [level]) ( select base, parent ancestor, 1 [level] sample_data base of operations = 'test1' -- start here union select ntl.base, nt.parent, ntl.[level] + 1 [level] nt_list ntl bring together sample_data nt on ntl.ancestor = nt.base nt.parent <> ntl.base -- prepare recursive bug (e.g. 1 -> 2 -> 1) -- tried add together cannot: (e.g. 1 -> 2 -> 3 -> 2) , nt.parent in (select ancestor nt_list) ) select distinct ntl.base, ntl.ancestor nt_list ntl order ancestor

sql error: recursive fellow member of mutual table look 'nt_list' has multiple recursive references.

you can use

;with nt_list (base, ancestor, [level], cycle, path) (select base, parent ancestor, 1 [level], 0 cycle, cast('.' varchar(max)) + isnull(parent, '') + '.' + base of operations + '.' [path] notetest base of operations = 'test1' union select ntl.base, nt.parent, ntl.[level] + 1 [level], case when ntl.[path] '%.' + ltrim(nt.base) + '.%' 1 else 0 end cycle, ntl.[path] + ltrim(nt.base) + '.' [path] nt_list ntl bring together notetest nt on ntl.ancestor = nt.base , ntl.cycle = 0) select ntl.base, ntl.ancestor nt_list ntl order ancestor

sql common-table-expression recursive-query