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

Question

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 

Answer 1

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