SQL Server: How to get all child records given a parent id in a self referencing table

Viewed 62042

Hi 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
3 Answers
Related