Как получить всех детей родителя, а затем их детей, используя рекурсию в запросе

У меня есть структура вроде этого:

<Unit>
  <SubUnit1>
           <SubSubUnit1/>
           <SubSubUnit2/>
           ...
           <SubSubUnitN/>
  </SubUnit1/>
  <SubUnit2>
           <SubSubUnit1/>
           <SubSubUnit2/>
           ...
           <SubSubUnitN/>
  </SubUnit2/>
  ...
  <SubUnitN>
           <SubSubUnit1/>
           <SubSubUnit2/>
           ...
           <SubSubUnitN/>
  </SubUnitN/>
</Unit>

Эта структура имеет 3 уровня: основной блок, SubUnits и SubSubUnits.

Я хочу выбрать всех детей по UnitId.
Если я ищу по Unit, я должен получить все дерево.
Если я ищу SubUnit1, я должен получить SubUnit1 и всех детей SubUnit1.
Если я ищу SubSubUnit2, я должен сам получить.

Вот моя попытка:

with a(id, parentid, name)
as (
select id, parentId, name
   from customer a
   where parentId is null 
union all
   select a.id, a.parentid, a.Name
   from customer
     inner join a on customer.parentId = customer.id
    )
select parentid, id, name 
from customer pod
where pod.parentid in (
select id
from customer grbs
where grbs.parentid in (
select id
from customer t
where t.parentid = @UnitId
))
union 
select parentid, id, name
from customer grbs
where grbs.parentid in (
select id
from customer t
where t.parentid = @UnitId
)
union
select parentid, id, name
from customer c
where c.Id = @UnitId
order by parentid, id

Я использую 3 союзных слова, это не хорошо, но это работает. Структура случая будет иметь N уровней, как я должен получить правильный результат?

Ответ 1