Path Query In Cyclic Graph? --recursive CTE

Hi

I wrote a simple sql query to get the shortest path length from node 1 to all the other nodes. Since there's a loop in the graph, I want to prevent it from going back to some nodes it has expanded before.

I got the following error:

Msg 253, Level 16, State 1, Line 2
Recursive member of a common table expression 'CTE_Sample' has multiple recursive references.

It is referring to "and Table1.t NOT IN (select fr from CTE_Sample)"

Can somebody help me to solve it?

btw. How to use the UNION, EXCEPT or INTERSECT operators when doing the recursive join? It seems I must use UNION ALL.



Thanks!

drop table Table1
CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 5)

INSERT Table1 VALUES (4, 2)

GO

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
and Table1.t NOT IN (select fr from CTE_Sample)
)
SELECT CTE_Sample.t, min(CTE_Sample.level)
FROM CTE_Sample
group by CTE_Sample.t