tsql - I got stuck on cycles to get the path of a sql directed graph -

tsql - I got stuck on cycles to get the path of a sql directed graph -

i've got table maintain couples of numbers, indicate arcs of directed graph, every node identified integer value:

create table graph ( n2 integer not null, n1 integer not null, primary key (id_area_possesso, id_area_utente) constraint ck check (n1 <> n2) )

where n1 points n2, , on; so, instance, when insert

insert graph values (3,4) insert graph values (9,3) insert graph values (12,9)

i obtain graph: 4->3->9->12.

i utilize query list of arcs (the path) starting node:

with tmp (n2,n1) ( select g.n2 , g.n1 graph g n1=3 union select n2 , n1 graph g bring together tmp on (g.n1=tmp.n2) ) select * tmp go

as result of query obtain arcs:

(9,3) (12,9)

this query works fine, when there cycles on graph:

insert graph values (0,1) insert graph values (2,0) insert graph values (1,2)

it goes on infinite loop, , error message:

the statement terminated. maximum recursion 100 has been exhausted before statement completion.

i can't utilize or create other tables in project, have on temporary ones. how can prepare query in order right path, avoiding stuck in cycles?

you can build string id's go , test if id in.

;with tmp (n2,n1,nx) ( select g.n2, g.n1, cast('/'+cast(g.n1 varchar(10))+'/' varchar(max)) graph g n1=1 union select g.n2, g.n1, tmp.nx+cast(g.n1 varchar(10)) +'/' graph g bring together tmp on (g.n1=tmp.n2) , tmp.nx not '%/'+cast(g.n1 varchar(10))+'/%' ) select * tmp

sql tsql graph