query to get all childs and grand children for a prent

Question

Post reply

query to get all childs and grand children for a prent

sql_learner29

SSCrazy Eights

Points: 9107
More actions
December 10, 2010 at 9:23 am

#232447

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
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11393 More actions · Answer 1

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/

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

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

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Mike01 SSChampion Points: 11393 More actions · Answer 3

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/

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

Hi all..

here is the solution...

http://stackoverflow.com/questions/1686340/sql-server-how-to-get-all-child-records-given-a-parent-id-in-a-self-referencing

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 5

VSSGeorge

SSCrazy Eights

Points: 8146

December 15, 2010 at 11:17 pm

#1263308

Hi

Thats a great link..Thanks...

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tommyh SSCertifiable Points: 6302 More actions · Answer 7

Jeff Moden (12/15/2010)
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.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

@tommyh

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tommyh SSCertifiable Points: 6302 More actions · Answer 9

Jeff Moden (12/16/2010)
@tommyh
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.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jayraj0071 SSC Journeyman Points: 86 More actions · Answer 12

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

praveen121 Newbie Points: 2 More actions · Answer 13

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))

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

praveen121 wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)