This is another alternative that will show the required hierarchy and highlight where circular references begin -

WITH GroupMembers (Child, ParentGroup, Level, hierarchypath)

AS

(-- Anchor member definition

SELECTg.child,

g.parent,

0 AS Level,

convert(varchar(max), g.child + '-&gt' + g.parent) AS hierarchypath

FROM ChildrenAndParents AS g

WHERE child not in (select parent from ChildrenAndParents)

UNION ALL

-- Recursive member definition

SELECTg.child,

g.parent,

Level + 1,

hierarchypath + '-&gt' + g.parent -- Add '--&gt...' to end when recursion found

+ Case When gm.hierarchypath like '%->'+g.parent+'-&gt%' Then '--&gt...'

Else''

End

FROM ChildrenAndParents as g

INNER JOIN GroupMembers AS gm

ON gm.parentgroup = g.child

--Exclude if the hierarchypath text contains a recursion

where gm.hierarchypath not like '%-&gt...'

)

which gives -

child parentgroup level hierarchypath

--------- ----------- ----------- -----------------------

a b 0 a->b

a c 1 a->b->c

a d 2 a->b->c->d

a b 3 a->b->c->d->b-->...

e b 0 e->b

e c 1 e->b->c

e d 2 e->b->c->d

e b 3 e->b->c->d->b-->...

-------

Something I can't get my head round - can anyone explain ...

&nbsp &nbsp The result of running the first CTE before the circular relationship was added had extra rows reported that aren't there when the circular check is added. E.g.

child parentgroup level hierarchypath

--------- ----------- ----------- -----------------------

b c 0 b->c

c d 0 c->d

etc

Can anyone explain why they were there/why they were removed?

Andy