How to write a query which finds all circular references when a table references itself? (II)
I think you're looking for a recursive common table expression.

WITH rcte (Id, ParentId, [Path])
AS ( --- Anchor:
     SELECT Id, ParentId,
            CAST(CAST(Id AS varchar(10))+' &gt; '+
         CAST(ParentID AS varchar(10)) AS varchar(max))
     FROM MyTable

     UNION ALL

     --- Recursion:
     SELECT t.Id, rcte.ParentId,
        CAST(CAST(t.Id AS varchar(10))+' &gt; '+
         rcte.[Path] AS varchar(max))
     FROM rcte
     INNER JOIN MyTable AS t ON rcte.Id=t.ParentId
     WHERE rcte.ParentID!=rcte.Id)

--- Returns rows where parent is child, i.e. self-recursion:
SELECT ISNULL(b.Id, a.Id) AS Id, ISNULL(b.[Path]+SUBSTRING(a.[Path], LEN(CAST(a.Id AS varchar(10)))+1, LEN(a.[Path])), a.[Path])
FROM rcte AS a
LEFT JOIN rcte AS b ON b.ParentId=a.Id AND b.Id NOT IN (SELECT ParentId FROM MyTable)
WHERE a.Id=a.ParentId
ORDER BY a.Id
OPTION (MAXRECURSION 0);


First off, every relation is a potential anchor in the tree. Then union those anchors to their respective relations (working your way down the tree for each recursion). The recursion stops when Id is ParentId, i.e. when you've come "full circle", or when there is nothing left to recurse.

Finally, the returned resultset is filtered for only those records where Id=ParentID. You might want to add a filter to exclude the 0-member as well, not sure how you wanted it.

Edit: I've LEFT JOIN'ed rcte again (as "b") to catch any branches leading up to a self-recursion. The query still doesn't precisely return the data in the question, but perhaps it's close enough. :)

A quick intro to recursive CTEs from a blog post of mine.