CODEWITHSUNDEEP
sqlsql-server-2005hierarchical-data
Programmer
Programmer

Reputation: 503

SQL Query for Parent Child Relationship

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

Upvotes: 13

Views: 32545

Answers (4)

Raju
Raju

Reputation: 11

 WITH Temp_Menu AS
 ( 
  SELECT AM.* from FCB_AccessMenu AM where AM.[ParentId] = 6   

           UNION ALL     

        SELECT AM.* FROM  FCB_AccessMenu AM ,Temp_Menu TM  WHERE AM.[ParentID]=TM.[MenuID]        

  )   

  SELECT * FROM Temp_Menu ORDER BY ParentID

Upvotes: 1

Coolcoder
Coolcoder

Reputation: 4036

And just to make sure it works if its a parent of itself (otherwise it will recurse until it breaks): 

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

Upvotes: 4

Keith Palmer Jr.
Keith Palmer Jr.

Reputation: 27952

You should look into using the Nested Set Model for parent-child relationships within an SQL database. It's much nicer than trying to store the parentID of records in the table like this, and makes queries like this much easier.

Upvotes: 6

yfeldblum
yfeldblum

Reputation: 65435

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]

Upvotes: 10

Related Questions