SQL Server: How to get all child records given a parent id in a self referencing table
60,541
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
Author by
Matthew Dresser
Sitecore Technology MVP 2019 Sitecore Technology MVP 2018 Sitecore Technical MVP 2017 Sitecore Technical MVP 2016 I develop websites using Sitecore, ASP.NET MVC (and WebForms). I also use CSS, jQuery and other JavaScript frameworks.
Updated on July 09, 2022Comments
-
Matthew Dresser almost 2 years
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