CODEWITHSUNDEEP
sqlsql-server-2008
eng. Yahia Galal
eng. Yahia Galal

Reputation: 103

Get All Node Related with Parent in SQL

I have table Acc_Region.

enter image description here

I need to select all nodes related with the Parent -Delta Sector- when I use query as 

select  Name from Acc_Region where ID = 1

get all cites 

Cairo
Faysl City
Helwan City
Giza
Aiat
Saf

I try to use:

WITH RECURSIVE Region (ID, name, Parent_ID) AS
(
    SELECT ID, 
           name, 
           Parent_ID
    FROM ACC_REGION 
    UNION ALL
    SELECT Acc.ID, 
           Acc.name,
           Acc.Parent_ID
    FROM ACC_REGION Acc
      JOIN Region Reg ON Acc.userid = Reg.managerid
)
SELECT *FROM ACC_REGION

But there are problem with RECURSIVE.

Is there any way to solve this problem?

Upvotes: 0

Views: 135

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

This is how the recursive CTE should be constructed:

WITH RECURSIVE_Region(ID, NAME, PARENT_ID) AS
(
   -- Anchor member: get root node
   SELECT ID, NAME, PARENT_ID
   FROM Acc_Region
   WHERE ID = 1

   UNION ALL 

   -- Recursive member: get nodes of next level   
   SELECT t1.ID, t1.NAME, t1.PARENT_ID
   FROM Acc_Region AS t1
   JOIN RECURSIVE_Region AS t2 ON t1.PARENT_ID = t2.ID
)
SELECT *
FROM RECURSIVE_Region

Upvotes: 2

Related Questions