Sunil Singh Sunil Singh - 5 months ago 6
SQL Question

How to find root parent for all direct or indirect child in SQL Server table?

I want to write a stored procedure in SQL Server to find the following output using the procedure here child depth can be up to n level and my requirement is to find root parent for all child that is direct or indirect connected to parent:

Parent id child id notes2
-----------------------------------
11000 12000 notes1
11000 12100 notes2
11000 12200
11000 12250
11000 12300
11000 12350
11000 13005
11000 13006
13000 13001
13000 13002
13000 13003
13000 13004
13000 13005


My table structure and data is as -

CREATE TABLE [dbo].[parent_tbl]
(
[parent_id] [nvarchar](50) NOT NULL,
[description] [nvarchar](max) NULL,
[notes] [nvarchar](max) NULL,

CONSTRAINT [PK_parent_tbl]
PRIMARY KEY CLUSTERED ([parent_id] ASC)
)
GO

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'11000', N'item1', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'12000', N'item3', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'12100', N'item2', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'12200', N'item4', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'12250', N'item5', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'12300', N'item6', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'12350', N'item7', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13000', N'item8', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13001', N'item9', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13002', N'item10', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13003', N'item11', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13004', N'item', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13005', N'item', NULL)

INSERT [dbo].[parent_tbl] ([parent_id], [description], [notes])
VALUES (N'13006', N'a', NULL)


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[child_tbl]
(
[parent_id] [nvarchar](50) NOT NULL,
[child_id] [nvarchar](50) NOT NULL,
[notes2] [nvarchar](max) NULL
)
GO

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'11000', N'12000', N'notes1')

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'11000', N'12100', N'notes2')

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'11000', N'12200', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'12200', N'12250', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'12200', N'12300', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'12300', N'12350', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'13000', N'13001', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'13001', N'13002', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'13001', N'13003', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'13004', N'13005', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'12300', N'13005', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'12200', N'13006', NULL)

INSERT [dbo].[child_tbl] ([parent_id], [child_id], [notes2])
VALUES (N'13001', N'13004', NULL)

Answer

recursive cte's are useful for retrieving hierarchical data

;WITH cte AS (
    SELECT  pt.parent_id,
            ct.notes2,
            ct.child_id
    FROM    [parent_tbl] pt
            JOIN [child_tbl] ct ON pt.parent_id = ct.parent_id
            LEFT JOIN [child_tbl] ct2 ON pt.parent_id = ct2.child_id
    WHERE   ct2.parent_id IS NULL -- only get parent id's that are not children
    UNION ALL
    SELECT  cte.parent_id,
            ct.notes2,
            ct.child_id
    FROM    cte
            JOIN [child_tbl] ct ON cte.child_id = ct.parent_id  
)
SELECT  parent_id, child_id, notes2 
FROM    cte 
ORDER BY parent_id, child_id