sqlsql-server-2005recursionhierarchy

Get all child records given a parent id in a self referencing table


I have a table which references itself and I need to be able to select the parent and all it's child records from a given parent Id.

My table is as follows:

ID   | ParentID | Name         
-----------------------              
1      NULL       A
2      1          B-1
3      1          B-2
4      2          C-1
5      2          C-2

So for the above example I'd like to be able to pass in a value of 1 and get all the records above.

So far, I've come up with the following recursive table-valued-function but it's not behaving as expected (only returning the first record).

CREATE FUNCTION [dbo].[SelectBranches]
(   
    @id INT
    ,@parentId INT
)
RETURNS @branchTable TABLE
(
    ID INT
    ,ParentID INT
    ,Name INT
)
AS
BEGIN 

    IF @branchId IS NOT NULL BEGIN

        INSERT INTO @branchTable
        SELECT 
            ID
            ,ParentID
            ,Name
        FROM
            tblLinkAdvertiserCity
        WHERE
            ID = @id

    END

    INSERT INTO @branchTable
    SELECT
        br.ID
        ,br.ParentID
        ,br.Name
    FROM
        @branchTable b
    CROSS APPLY
        dbo.SelectBranches(NULL, b.ParentID) br

    RETURN
END
GO

Solution

  • You can try this

    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