andy.roberts (1/31/2011)
Something I can't get my head round - can anyone explain ...
    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
Since b and c both exist in the parent column of the underlying table, they shouldn't appear in the first pass. The only way I can get the extra rows you've mentioned into the CTE results is if I comment out the line
WHERE child not in (select parent from childrenandparents)
from the anchor definition. I don't suppose that could have happened whilst you were streamlining my CTE code, could it?
Semper in excretia, suus solum profundum variat