get child items of a recursive table in sql

Question

I have a table with some level (up to 5 level) as shown below. I want to create a procedure that gets some id and returns the items and their children... I have no idea how to handle it! (when refid is 1, it means that the node is a parent in first level)

enter image description here

@HamletHakobyan Sorry I forgot to mention it. I updated the question. — mjyazdani, Oct 19 '14 at 10:44
[T-SQL Query : Getting Child nodes of a parent](http://stackoverflow.com/questions/3601900/t-sql-query-getting-child-nodes-of-a-parent) or [How to get all the child IDs](http://stackoverflow.com/questions/11435788/how-to-get-all-the-child-ids) — bummi, Oct 19 '14 at 11:15

score 1 · Accepted Answer · answered Oct 20 '14 at 04:26

    DECLARE @Table TABLE(
    ID INT,
    ParentID INT,
    NAME VARCHAR(20)
)

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 1, NULL, 'A'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 2, 1, 'B-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 3, 1, 'B-2'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 4, 2, 'C-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 5, 2, 'C-2'


DECLARE @ID INT

SELECT @ID = 2

;WITH ret AS(
        SELECT  *
        FROM    @Table
        WHERE   ID = @ID
        UNION ALL
        SELECT  t.*
        FROM    @Table t INNER JOIN
                ret r ON t.ParentID = r.ID
)

SELECT  *
FROM    ret

get child items of a recursive table in sql

1 Answers1