December 10, 2010 at 9:23 am
hi
SQL Server: How to get all child records given a parent id in a self referencing table
Can any one help me to write a recursive query to get all clidren and grand children in a parent...
its a milti level heirarchy,
I will pass parent as parameter and the result should return all child under that parent + under the child if there are any ..
Any help
Thanks [/font]
December 10, 2010 at 9:50 am
How about supplying table scripts and data?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 10, 2010 at 10:55 am
Data looks sommthing like this:
Parent Id Child_Id
2 3
2 4
2 5
6 7
6 8
6 9
10 11
10 12
10 13
I got the answer anyways,,,using Recursive CTE
Thanks [/font]
December 10, 2010 at 11:45 am
Could you post your solution? If anyone finds this thread in the future, they will have a solution. Thanks and I'm happy to hear you found your answer
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2010 at 2:36 pm
Hi all..
here is the solution...
Thanks [/font]
December 15, 2010 at 11:17 pm
Hi
Thats a great link..Thanks...
December 15, 2010 at 11:54 pm
Learner1 (12/10/2010)
I got the answer anyways,,,using Recursive CTE
IMHO... [font="Arial Black"]ABSOLUTELY NOT!!! [/font]:-P
While it certainly is easy to write an rCTE to do this, an rCTE will use a heck of a lot more reads than a simple while loop. It's quite late here and I'm getting ready to hit the hay so I won't post an example tonight (such examples take a good amount of time to be clear). If you're interested, post back and I'll see what I can do tomorrow night.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2010 at 2:07 am
Jeff Moden (12/15/2010)
Learner1 (12/10/2010)
I got the answer anyways,,,using Recursive CTEIMHO... [font="Arial Black"]ABSOLUTELY NOT!!! [/font]:-P
While it certainly is easy to write an rCTE to do this, an rCTE will use a heck of a lot more reads than a simple while loop. It's quite late here and I'm getting ready to hit the hay so I won't post an example tonight (such examples take a good amount of time to be clear). If you're interested, post back and I'll see what I can do tomorrow night.
Okay a bit strong. Isnt that a lot depending on circumstances? Well from what i have been able to read up on it, its one of those "it depends".
My own little test showed the CTE to be faster. But then my skills are limited. The little table/data that i tried against was
create table #ppl (ParentId integer primary key, ChildId integer)
insert into #ppl (ParentId, ChildId)
select 1, null union all
select 2, 3 union all
select 3, null union all
select 4, 6 union all
select 5, null union all
select 6, 7 union all
select 7, 5 union all
select 8, 1 union all
select 9, null
create index ppl_idx on #ppl (ChildId, ParentId)
And i tried to get the data for Parentid = 4. Which gave me 54 Reads on the CTE and 167 (166) on the WHILE.
The CTE
declare @GetParent integer
select @GetParent = 4;
;with cte as
(
select 1 lvl, ParentId, ChildId from #ppl where Parentid = @GetParent
union all
select cte.lvl + 1, #ppl.ParentId, #ppl.ChildId from #ppl join cte on cte.ChildId = #ppl.ParentId
)
select * from cte
The WHILE
declare @GetParent integer
select @GetParent = 4;
declare @ret table (lvl integer, ParentId integer, ChildId integer, primary key (lvl, ParentId))
declare @lvl integer
set @lvl = 1
insert into @ret (lvl, ParentId, ChildId) select @lvl, @GetParent, ChildId from #ppl where ParentId = @GetParent
while (@@ROWCOUNT > 0)
begin
set @lvl = @lvl + 1
insert into @ret (lvl, ParentId, ChildId)
select @lvl, p.ParentId, p.ChildId
from #ppl p
join @ret r on r.lvl = @lvl - 1 and p.ParentId = r.ChildId
end
select * from @ret
So if anything i for one would like to know more about why "[font="Arial Black"]ABSOLUTELY NOT!!! [/font]:-P"
/T
December 16, 2010 at 10:38 pm
Thanks for the feedback. You are, of course right about "It Depends". Guess I'll have to yank out some examples of what I ran into on larger hierarchies. It'll take me a bit because the examples I currently have are for million node hierarchies and batch runs. I'll need to whittle that down a bit and I have to get up in about 4 hours so it's just not going to happen tonight.
As a bit of a side bar... neither the data that Learner1 posted nor your's actually form hierarchies. Learner1's example (yes, I realize it's just an example and may not represent the actual data) is a table where each parent has only one level of children and that's it because none of the parents are also children. Worse yet, you get a primary key violation if you try to insert that data into the accompanying table.
Your table isn't hierarchical either. Each parent can only exist one time because of the primary key. That means that although children can be parents, they can only have one child. That forms a simple straight line with no chance of branches like a hierarchy would have.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2010 at 11:39 pm
Jeff Moden (12/16/2010)
@tommyhThanks for the feedback. You are, of course right about "It Depends". Guess I'll have to yank out some examples of what I ran into on larger hierarchies. It'll take me a bit because the examples I currently have are for million node hierarchies and batch runs. I'll need to whittle that down a bit and I have to get up in about 4 hours so it's just not going to happen tonight.
As a bit of a side bar... neither the data that Learner1 posted nor your's actually form hierarchies. Learner1's example (yes, I realize it's just an example and may not represent the actual data) is a table where each parent has only one level of children and that's it because none of the parents are also children. Worse yet, you get a primary key violation if you try to insert that data into the accompanying table.
Your table isn't hierarchical either. Each parent can only exist one time because of the primary key. That means that although children can be parents, they can only have one child. That forms a simple straight line with no chance of branches like a hierarchy would have.
You are off course right about the 1 parent 1 child. In a normal situation there would be a secondary table holding the hierarcy... but i was just lazy getting something that i could loop through 😛
/T
December 18, 2010 at 5:12 pm
Weeeelllll CRUD! I can't duplicate the problem I was having before. Yes, it's easy to duplicate the fact that the reads for a CTE will be twice as much as the While Loop just like I said, but I can't duplicate the problem of the CTE using more time than the While Loop in a Hierarchical example.
If you'd like to play, here's a test data generator that builds a 1000 row hierarchy to play with...
--===== Do this in a nice safe place that everyone has.
USE TempDB;
/******************************************************************************
Create a randomized "clean" hierarchy. Each EmployeeID (except the first one,
of course) is assigned a random ParentID number which is always less than the
current EmployeeID. This code runs nasty fast and is great for testing
hierarchical processing code. --Jeff Moden
******************************************************************************/
--===== Conditionaly drop the test table so we can do reruns more easily
IF OBJECT_ID('TempDB.dbo.Employee','U') IS NOT NULL
DROP TABLE TempDB.dbo.Employee
;
--===== Build the test table and populate it on the fly.
-- Everything except ParentID is populated here.
SELECT TOP (1000)
IDENTITY(INT,1,1) AS EmployeeID,
CAST(0 AS INT) AS ParentID,
CAST(NEWID() AS VARCHAR(36)) AS EmployeeName,
(ABS(CHECKSUM(NEWID()))%12+1)*1000 AS Sales
INTO TempDB.dbo.Employee
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2
;
--===== Update the test table with ParentID's. The ParentID is some random
-- value which is always less than the current EmployeeID to keep the
-- hierarchy "clean" and free from "loop backs".
UPDATE TempDB.dbo.Employee
SET ParentID = CASE
WHEN EmployeeID >1
THEN ABS(CHECKSUM(NEWID()))%(EmployeeID-1)+1
ELSE NULL
END
;
--===== Add some indexes that most folks would like have on such a table
ALTER TABLE TempDB.dbo.Employee
ADD CONSTRAINT PK_Employee
PRIMARY KEY CLUSTERED (EmployeeID)
;
CREATE INDEX IX_Employee_ParentID
ON TempDB.dbo.Employee (ParentID)
;
ALTER TABLE dbo.Employee WITH CHECK
ADD CONSTRAINT FK_Employee_Employee
FOREIGN KEY(ParentID) REFERENCES dbo.Employee (EmployeeID)
;
GO
Here's the code I've been testing with. No timings buried in the code... I was using SQL Profiler to get the Duration, CPU, Reads, Writes, and Rowcounts for SQL:BatchCompleted.
-------------------------------------------------------------------------------
--===== Supress the autodisplay of rowcounts
SET NOCOUNT ON
;
GO
--===== Recursive CTE =========================================================
WITH
cteBuildSortPath AS
(--==== This is the "anchor" part of the recursive CTE
SELECT anchor.EmployeeID,
anchor.ParentID,
1 AS HLevel,
CAST(anchor.EmployeeID AS VARCHAR(8000)) AS SortPath
FROM dbo.Employee AS anchor
WHERE EmployeeID = 1
UNION ALL
--==== This is the "recursive" part of the CTE that adds 1 for each level
-- and concatenates each level of EmployeeID's to the SortOrder column.
SELECT recur.EmployeeID,
recur.ParentID,
cte.HLevel + 1 AS HLevel,
cte.SortPath + '/' + CAST(recur.EmployeeID AS VARCHAR(8000)) AS SortPath
FROM dbo.Employee AS recur
INNER JOIN cteBuildSortPath AS cte
ON cte.EmployeeID = recur.ParentID
)--==== Send the result to a temp table to take the time to display
-- out of the picture.
SELECT *
FROM cteBuildSortPath
;
GO
-------------------------------------------------------------------------------
--===== Conditionally drop temp table to make reruns easier
IF OBJECT_ID('TempDB..#TargetTable') IS NOT NULL
DROP TABLE #TargetTable
;
GO
--===== While Loop ============================================================
--===== Create the Hierarchy table
CREATE TABLE #TargetTable
(
EmployeeID INT,
ParentID INT,
HLevel INT,
SortPath VARCHAR(8000)
)
;
--===== Create and preset a Hierarchy Level counter.
DECLARE @CurrentHierarchyLevel INT
SET @CurrentHierarchyLevel = 1
;
--===== Seed the Hierarchy table with the top HierarchyLevel
INSERT INTO #TargetTable
(EmployeeID, ParentID, HLevel, SortPath)
SELECT EmployeeID,
ParentID,
1 AS HierarchyLevel,
CAST(EmployeeID AS VARCHAR(8000)) AS SortPath
FROM dbo.Employee
WHERE EmployeeID = 1
;
--===== Determine the rest of the hierarchy
-- The loop processes sets of rows based on Hierarchy Level.
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentHierarchyLevel = @CurrentHierarchyLevel + 1
INSERT INTO #TargetTable
(EmployeeID, ParentID, HLevel, SortPath)
SELECT emp.EmployeeID,
emp.ParentID,
@CurrentHierarchyLevel AS HLevel,
tgt.SortPath + '/' + CAST(emp.EmployeeID AS VARCHAR(10)) AS SortPath
FROM dbo.Employee AS emp
INNER JOIN #TargetTable AS tgt
ON emp.ParentID = tgt.EmployeeID
AND tgt.HLevel = @CurrentHierarchyLevel - 1
END
;
--===== Finally, select the output
SELECT * FROM #TargetTable
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2010 at 6:04 pm
Ah! Now I remember. I haven't lost my mind after all! 😛
I was building an "interim step table" while converting a million row "Adjacency List" (Parent/Child model) to a "Hierarchical Path" model as an interim step to creating a "Nested Set" model. The While Loop beat the Recursive CTE on all counts except Writes there.
Here's the code to build a million row "Adjacency List". It's the same as before except I changed "10000" to "1000000".
--===== Do this in a nice safe place that everyone has.
USE TempDB;
/******************************************************************************
Create a randomized "clean" hierarchy. Each EmployeeID (except the first one,
of course) is assigned a random ParentID number which is always less than the
current EmployeeID. This code runs nasty fast and is great for testing
hierarchical processing code. --Jeff Moden
******************************************************************************/
--===== Conditionaly drop the test table so we can do reruns more easily
IF OBJECT_ID('TempDB.dbo.Employee','U') IS NOT NULL
DROP TABLE TempDB.dbo.Employee
;
--===== Build the test table and populate it on the fly.
-- Everything except ParentID is populated here.
SELECT TOP (1000000)
IDENTITY(INT,1,1) AS EmployeeID,
CAST(0 AS INT) AS ParentID,
CAST(NEWID() AS VARCHAR(36)) AS EmployeeName,
(ABS(CHECKSUM(NEWID()))%12+1)*1000 AS Sales
INTO TempDB.dbo.Employee
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2
;
--===== Update the test table with ParentID's. The ParentID is some random
-- value which is always less than the current EmployeeID to keep the
-- hierarchy "clean" and free from "loop backs".
UPDATE TempDB.dbo.Employee
SET ParentID = CASE
WHEN EmployeeID >1
THEN ABS(CHECKSUM(NEWID()))%(EmployeeID-1)+1
ELSE NULL
END
;
--===== Add some indexes that most folks would like have on such a table
ALTER TABLE TempDB.dbo.Employee
ADD CONSTRAINT PK_Employee
PRIMARY KEY CLUSTERED (EmployeeID)
;
CREATE INDEX IX_Employee_ParentID
ON TempDB.dbo.Employee (ParentID)
;
ALTER TABLE dbo.Employee WITH CHECK
ADD CONSTRAINT FK_Employee_Employee
FOREIGN KEY(ParentID) REFERENCES dbo.Employee (EmployeeID)
;
GO
Here's almost the same code as before. The main difference is that I added an INTO to the Recursive CTE to build the interim table on the fly and removed the final display SELECT from the While Loop because it wasn't necessary for the task at hand. I also added DBCC FREEPROCCACHE to both parts of the code so as to NOT give the While Loop an unfair advantage...
-------------------------------------------------------------------------------
--===== Supress the autodisplay of rowcounts
SET NOCOUNT ON
;
--===== Conditionally drop temp table to make reruns easier
IF OBJECT_ID('TempDB..#TargetTableCte') IS NOT NULL
DROP TABLE #TargetTableCte
;
DBCC FREEPROCCACHE
;
GO
--===== Recursive CTE =========================================================
WITH
cteBuildSortPath AS
(--==== This is the "anchor" part of the recursive CTE
SELECT anchor.EmployeeID,
anchor.ParentID,
1 AS HLevel,
CAST(anchor.EmployeeID AS VARCHAR(8000)) AS SortPath
FROM dbo.Employee AS anchor
WHERE EmployeeID = 1
UNION ALL
--==== This is the "recursive" part of the CTE that adds 1 for each level
-- and concatenates each level of EmployeeID's to the SortOrder column.
SELECT recur.EmployeeID,
recur.ParentID,
cte.HLevel + 1 AS HLevel,
cte.SortPath + '/' + CAST(recur.EmployeeID AS VARCHAR(8000)) AS SortPath
FROM dbo.Employee AS recur
INNER JOIN cteBuildSortPath AS cte
ON cte.EmployeeID = recur.ParentID
)--==== Send the result to a temp table to take the time to display
-- out of the picture.
SELECT *
INTO #TargetTableCte
FROM cteBuildSortPath
;
GO
-------------------------------------------------------------------------------
--===== Conditionally drop temp table to make reruns easier
IF OBJECT_ID('TempDB..#TargetTableLoop') IS NOT NULL
DROP TABLE #TargetTableLoop
;
DBCC FREEPROCCACHE
;
GO
--===== While Loop ============================================================
--===== Create the Hierarchy table
CREATE TABLE #TargetTableLoop
(
EmployeeID INT,
ParentID INT,
HLevel INT,
SortPath VARCHAR(8000)
)
;
--===== Create and preset a Hierarchy Level counter.
DECLARE @CurrentHierarchyLevel INT
SET @CurrentHierarchyLevel = 1
;
--===== Seed the Hierarchy table with the top HierarchyLevel
INSERT INTO #TargetTableLoop
(EmployeeID, ParentID, HLevel, SortPath)
SELECT EmployeeID,
ParentID,
1 AS HierarchyLevel,
CAST(EmployeeID AS VARCHAR(8000)) AS SortPath
FROM dbo.Employee
WHERE EmployeeID = 1
;
--===== Determine the rest of the hierarchy
-- The loop processes sets of rows based on Hierarchy Level.
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentHierarchyLevel = @CurrentHierarchyLevel + 1
INSERT INTO #TargetTableLoop
(EmployeeID, ParentID, HLevel, SortPath)
SELECT emp.EmployeeID,
emp.ParentID,
@CurrentHierarchyLevel AS HLevel,
tgt.SortPath + '/' + CAST(emp.EmployeeID AS VARCHAR(10)) AS SortPath
FROM dbo.Employee AS emp
INNER JOIN #TargetTableLoop AS tgt
ON emp.ParentID = tgt.EmployeeID
AND tgt.HLevel = @CurrentHierarchyLevel - 1
END
;
Here are the SQL Profiler results from 3 separate runs.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2011 at 2:48 pm
Hi
Here is the possible solution.
create proc [dbo].[parent_child_relation]
@childid char(1)
as
begin
;with cte as
(select Parent,Child from parent_child c
where c.Child = @childid
union all
select c.* from cte p, parent_child c
where p.Parent = c.Child)
select * from cte
end
September 2, 2019 at 1:30 pm
the simple solution can be upto 3 childs
select id from table where id=11 or ParentId =11 or ParentId in(select id from table where parentid =11) or for more child you can add
select id from table where id=11 or ParentId =11 or ParentId in(select id from table where parentid in (select id from table where parentid =11))
September 2, 2019 at 4:30 pm
the simple solution can be upto 3 childs
True enough but all hell will break loose when you need to go to 4 "childs". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply