SQL Query for Parent Child Relationship

hierarchical-data, sql, sql-server-2005

Solution

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]

Problem

I have db table with parent child relationship as: ``` NodeId NodeName ParentId ------------------------------ 1 Node1 0 2 Node2 0 3 Node3 1 4 Node4 1 5 Node5 3 6 Node6 5 7 Node7 2 ``` 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. e.g. for nodeId = 1, it should return 3, 4, 5, 6. I am using MS SQL Server 2005

Original source

Related problems