You can still end up with an infinite loop with this (well, it will loop till it hits the recursion limit).
Table1 has an FK that references Table3
Table2 has an FK that references Table1
Table3 has an FK that references Table2
So long as at least one of these keys doesn't have a Not Null constraint on the column, this data structure is possible. It's most likely to happen in many-to-many-to-many relations.
What you're better off doing, if this kind of chain-key relationship is possible in your database, is a self-referent outer-join in the recursive portion of the CTE, with an Is Null in the Where clause, including the Level column in part of the join.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon