title: Select all parents or children in same table relation SQL Server
body: <p>SQL developers, I have a badly planned database as task to learn a lot about SQL Server 2012.</p>
  
  <p>SO, there is the table <code>Elem</code>:</p>
  
  <pre><code>+-----------+----+---+----------+------------+
  |VERSION(PK)|NAME|KEY|PARENT_KEY|DIST_KEY(FK)|
  +-----------+----+---+----------+------------+
  |1          |a   |12 |NULL      |1           |
  +-----------+----+---+----------+------------+
  |2          |b   |13 |12        |1           |
  +-----------+----+---+----------+------------+
  |3          |c   |14 |13        |1           |
  +-----------+----+---+----------+------------+
  |4          |d   |15 |12        |1           |
  +-----------+----+---+----------+------------+
  |5          |e   |16 |NULL      |1           |
  +-----------+----+---+----------+------------+
  |6          |e   |17 |NULL      |2           |
  +-----------+----+---+----------+------------+
  </code></pre>
  
  <p>After update the row I need to check parent key of element to not allow element to be self-granny or something.. </p>
  
  <p>And when I delete the row I need to delete all children and children of children, etc.</p>
  
  <p>Questions are:</p>
  
  <ol>
  <li><p>How can i select all "parent + grandparent + etc" of one element of DIST?</p></li>
  <li><p>How can i selects all "sons + grandsons + etc" of one element of DIST?</p></li>
  </ol>
  
  <p>I read about solutions with CTE, but I have no root of elements and I can't even understand how I can use CTE then. </p>
  
  <p>Please, help!</p>
  
  <p>Thanks.</p>