title: SQL Query for Parent Child Relationship
body: <p>I have db table with parent child relationship as:</p>
  
  <pre><code>NodeId    NodeName    ParentId
  ------------------------------
  1         Node1       0
  2         Node2       0
  3         Node3       1
  4         Node4       1
  5         Node5       3
  6         Node6       5
  7         Node7       2
  </code></pre>
  
  <p>Here parentId = 0 means that it is a root level node. Now I want to write an SQL Query which will return child at all level of a parent category.</p>
  
  <p>e.g. for nodeId = 1, it should return 3, 4, 5, 6.</p>
  
  <p>I am using MS SQL Server 2005 </p>