title: How to select parents and children rows in SQL Server
body: <p>I want to select all itens related to user_id 53 (parents and children) from the following table. It should be: 1, 2, 4, 8, 9.</p>
  
  <pre><code>my_table
  --------------------------------------------
  id    parent_id   user_id   sequence   depth
  --------------------------------------------
  1     null        50        1          1
  2     1           52        1.2        2
  3     1           52        1.3        2
  4     2           53        1.2.4      3
  5     2           52        1.2.5      3
  6     3           52        1.3.6      3
  7     3           51        1.3.7      3
  8     4           51        1.2.4.8    4
  9     4           51        1.2.4.9    4
  </code></pre>
  
  <p>With CTE I could select all children or parents, but I could'nt select children and parents with just one query. Below is the cte I'm using to select children.</p>
  
  <p>Item and children</p>
  
  <pre><code>with cte as (
      select t.id, t.parent_id, t.user_id 
      from my_table t 
      where t.user_id=53
  
      union all
  
      select t.id, t.parent_id, t.user_id 
      from my_table t
      inner join cte c on (c.parent_id=t.id)
  )
  select t.* from cte t;
  </code></pre>
  
  <p>Item and parents</p>
  
  <pre><code>with cte as (
      select t.id, t.parent_id, t.user_id 
      from my_table t 
      where t.user_id=53
  
      union all
  
      select t.id, t.parent_id, t.user_id 
      from my_table t
      inner join cte c on (c.id=t.parent_id)
  )
  select t.* from cte t;
  </code></pre>
  
  <p>Thanks.</p>