query to get all childs and grand children for a prent

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    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]

  • Mike01

    SSChampion

    Points: 11122

    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

    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: 11122

    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

    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: 8144

    Hi

    Thats a great link..Thanks...

  • Jeff Moden

    SSC Guru

    Points: 996475

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

  • tommyh

    SSCertifiable

    Points: 6252

    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: 996475

    @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)
    Forum FAQ

  • tommyh

    SSCertifiable

    Points: 6252

    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: 996475

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

  • Jeff Moden

    SSC Guru

    Points: 996475

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

  • jayraj0071

    SSC Journeyman

    Points: 86

    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

     

    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: 996475

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

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

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