Given a starting member, I can build a hierarchy of parents and children from for example a manager/employee table using a recursive CTE.
I'm trying to do the same with shareholders of a company which has the added complications:
Company A can own shares in itself
Company A can own shares in Company B while Company B owns shares in Company B.

All I thought I would need was another clause or join to the recursive query in my CTE to exclude parents that were already included but the CTE won't allow LEFT joins and won't allow me reference itself more than once:

Msg 253, Level 16, State 1, Line 1
Recursive member of a common table expression 'CTE_Hierarchy' has multiple recursive references.

Any ideas how I might do this without having to use a recursive stored procedure?