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

Question

Hi I have a 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

Answer 2

Unless you are using Oracle, your table structure is not suitable for the problem described. What you are attempting to do is grab a hierarchy (traversing a tree structure).

There is an article, More Trees & Hierarchies in SQL, that describes one method of solving the hierarchy problem. He basically adds a "lineage" column describing the hierarchy to every row.