title: How to get all children of a parent and then their children using recursion in query
body: <p>I have structure like this:<br/>
  <br/></p>
  
  <pre><code>&lt;Unit&gt;
    &lt;SubUnit1&gt;
             &lt;SubSubUnit1/&gt;
             &lt;SubSubUnit2/&gt;
             ...
             &lt;SubSubUnitN/&gt;
    &lt;/SubUnit1/&gt;
    &lt;SubUnit2&gt;
             &lt;SubSubUnit1/&gt;
             &lt;SubSubUnit2/&gt;
             ...
             &lt;SubSubUnitN/&gt;
    &lt;/SubUnit2/&gt;
    ...
    &lt;SubUnitN&gt;
             &lt;SubSubUnit1/&gt;
             &lt;SubSubUnit2/&gt;
             ...
             &lt;SubSubUnitN/&gt;
    &lt;/SubUnitN/&gt;
  &lt;/Unit&gt;
  </code></pre>
  
  <p>This structure has 3 levels: main Unit, SubUnits and SubSubUnits.</p>
  
  <p>I want to select all children by UnitId.<br/>
  If I search by Unit, I have to get all tree.<br/>
  If I search by SubUnit1, I have to get SubUnit1 and all children of SubUnit1.<br/>
  If I search SubSubUnit2, I have to get itself.<br/>
  <br/>
  Here is my try:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I use 3 union-words, it is not well but it works. Case structure will have N levels, how I have to get correct result?</p>