<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>

```
```
with [CTE] as (
    select * from [TheTable] c where c.[ParentId] = 1
    union all
    select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]
```
```