I have a train lines table which looks like this:
The task is to get every possible destination from a given Start (recursively).
I've tried to do it like this:
with cte (st, nd) as(
select from1, to1
from RailwayTbl
where from1 = 'Berlin'
union all
select e.from1, e.to1
from RailwayTbl e join cte
on cte.nd = e.from1
)
select * from cte;
When executed, I got the following error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. ((It is an infinite loop..))
And when I removed the all
and used union
only I got this error:
Recursive common table expression 'cte' does not contain a top-level UNION ALL operator.
Any ideas?
Thanks in advance!
You need to stop the recursion, which is a pain in SQL Server. The following keeps a string of the visited stations to stop the recursion:
with cte as (
select from1 as st, to1 as nd, concat(',', st, ',', to1) as dests, 1 as lev
from RailwayTbl
where from1 = 'Berlin'
union all
select e.from1, e.to1, concat(cte.dests, e.to1, ',') as dests, lev + 1
from RailwayTbl e join
cte
on cte.nd = e.from1
where dests not like concat('%,', e.nd, ',%')
)
select * from cte;
I also added lev
. Often I use where lev < 10
on the recursive part of the cte definition for debugging.