Ok not sure If I've got it right but if I add "where a.left not in (select id from dbo.ufn_Split(c.path) )" after the Inner join it appears to work ok....

Cheers Dwain. I'll take a look at your link and see if I can work it into my solution. This stuff just wrecks my head 🙂

Dwain (or anyone!)

Is there any way to detect an prevent this crashing on a circular reference? I need to have it support a circular ref but using this method above...

Thanks for that. Worked perfectly.

Sorry. The other one is in the wrong forum. I went back to delete it but there is no "delete" option. Should be in this forum only.

thanks,

thank you. I had something similar to that so was heading in the right direction but as they say the devil is in the details.

I'll check it against my...