Post reply

Help using CTE to get ALL childs in a hierarchy

  • August 27, 2008 at 6:20 am

    #191921

    I have this table:

    CREATE TABLE [mcmain].[eqmas](

    [eq_nmr] [char](10) NOT NULL,

    [eq_parent] [char](10) NULL,

    )

    GO

    INSERT mcmain.eqmas(eq_nmr, eq_parent)

    SELECT 'OB000274',' ' UNION ALL

    SELECT 'OB000275',' ' UNION ALL

    SELECT 'OB000286',' ' UNION ALL

    SELECT 'OB000037','OB000033' UNION ALL

    SELECT 'OB000038','OB000033' UNION ALL

    SELECT 'OB000039','OB000033' UNION ALL

    SELECT 'OB000040','OB000033' UNION ALL

    SELECT 'OB000041','OB000033' UNION ALL

    SELECT 'OB000042','OB000034' UNION ALL

    SELECT 'OB000043','OB000034' UNION ALL

    SELECT 'OB000044','OB000034' UNION ALL

    SELECT 'OB000045','OB000034' UNION ALL

    SELECT 'OB000046','OB000034' UNION ALL

    SELECT 'OB000047','OB000035' UNION ALL

    SELECT 'OB000048','OB000035' UNION ALL

    SELECT 'OB000049','OB000035' UNION ALL

    SELECT 'OB000050','OB000035' UNION ALL

    SELECT 'OB000051','OB000035' UNION ALL

    SELECT 'OB000052','OB000036' UNION ALL

    SELECT 'OB000053','OB000036' UNION ALL

    SELECT 'OB000054','OB000036' UNION ALL

    SELECT 'OB000055','OB000036' UNION ALL

    SELECT 'OB000056','OB000036' UNION ALL

    SELECT 'OB000099','OB000036' UNION ALL

    SELECT 'OB000100','OB000036' UNION ALL

    SELECT 'OB000101','OB000036' UNION ALL

    SELECT 'OB000102','OB000036' UNION ALL

    SELECT 'OB000103','OB000036' UNION ALL

    SELECT 'OB000104','OB000036' UNION ALL

    SELECT 'OB000165','OB000037' UNION ALL

    SELECT 'OB000073','OB000041' UNION ALL

    SELECT 'OB000257','OB000041' UNION ALL

    SELECT 'OB000258','OB000041' UNION ALL

    SELECT 'OB000259','OB000041' UNION ALL

    SELECT 'OB000122','OB000044' UNION ALL

    SELECT 'OB000081','OB000047' UNION ALL

    SELECT 'OB000059','OB000052' UNION ALL

    SELECT 'OB000061','OB000052' UNION ALL

    SELECT 'OB000062','OB000052' UNION ALL

    SELECT 'OB000063','OB000052' UNION ALL

    SELECT 'OB000064','OB000052' UNION ALL

    SELECT 'OB000065','OB000052' UNION ALL

    SELECT 'OB000067','OB000052' UNION ALL

    SELECT 'OB000068','OB000052' UNION ALL

    SELECT 'OB000070','OB000052' UNION ALL

    SELECT 'OB000071','OB000052' UNION ALL

    SELECT 'OB000076','OB000052' UNION ALL

    SELECT 'OB000077','OB000052' UNION ALL

    SELECT 'OB000079','OB000052' UNION ALL

    SELECT 'OB000080','OB000052' UNION ALL

    SELECT 'OB000082','OB000052' UNION ALL

    SELECT 'OB000084','OB000052' UNION ALL

    SELECT 'OB000086','OB000052' UNION ALL

    SELECT 'OB000087','OB000052' UNION ALL

    SELECT 'OB000089','OB000052' UNION ALL

    SELECT 'OB000091','OB000052' UNION ALL

    SELECT 'OB000093','OB000052' UNION ALL

    SELECT 'OB000095','OB000052' UNION ALL

    SELECT 'OB000125','OB000052' UNION ALL

    SELECT 'OB000155','OB000052' UNION ALL

    SELECT 'OB000156','OB000052' UNION ALL

    SELECT 'OB000158','OB000052' UNION ALL

    SELECT 'OB000160','OB000052' UNION ALL

    SELECT 'OB000162','OB000052' UNION ALL

    SELECT 'OB000163','OB000052' UNION ALL

    SELECT 'OB000272','OB000052' UNION ALL

    SELECT 'OB000166','OB000053' UNION ALL

    SELECT 'OB000168','OB000053' UNION ALL

    SELECT 'OB000169','OB000053' UNION ALL

    SELECT 'OB000171','OB000053' UNION ALL

    SELECT 'OB000173','OB000053' UNION ALL

    SELECT 'OB000174','OB000053' UNION ALL

    SELECT 'OB000175','OB000053' UNION ALL

    SELECT 'OB000176','OB000053' UNION ALL

    SELECT 'OB000178','OB000053' UNION ALL

    SELECT 'OB000180','OB000053' UNION ALL

    SELECT 'OB000182','OB000053' UNION ALL

    SELECT 'OB000184','OB000053' UNION ALL

    SELECT 'OB000186','OB000053' UNION ALL

    SELECT 'OB000187','OB000053' UNION ALL

    SELECT 'OB000188','OB000053' UNION ALL

    SELECT 'OB000189','OB000053' UNION ALL

    SELECT 'OB000190','OB000053' UNION ALL

    SELECT 'OB000191','OB000053' UNION ALL

    SELECT 'OB000192','OB000053' UNION ALL

    SELECT 'OB000193','OB000053' UNION ALL

    SELECT 'OB000195','OB000053' UNION ALL

    SELECT 'OB000197','OB000053' UNION ALL

    SELECT 'OB000198','OB000053' UNION ALL

    SELECT 'OB000200','OB000053' UNION ALL

    SELECT 'OB000201','OB000053' UNION ALL

    SELECT 'OB000265','OB000053' UNION ALL

    SELECT 'OB000267','OB000053' UNION ALL

    SELECT 'OB000268','OB000053' UNION ALL

    SELECT 'OB000203','OB000054' UNION ALL

    SELECT 'OB000205','OB000054' UNION ALL

    SELECT 'OB000206','OB000054' UNION ALL

    SELECT 'OB000208','OB000054' UNION ALL

    SELECT 'OB000209','OB000054' UNION ALL

    SELECT 'OB000211','OB000054' UNION ALL

    SELECT 'OB000212','OB000054' UNION ALL

    SELECT 'OB000214','OB000054' UNION ALL

    SELECT 'OB000216','OB000054' UNION ALL

    SELECT 'OB000217','OB000054' UNION ALL

    SELECT 'OB000270','OB000054' UNION ALL

    SELECT 'OB000119','OB000055' UNION ALL

    SELECT 'OB000218','OB000055' UNION ALL

    SELECT 'OB000220','OB000055' UNION ALL

    SELECT 'OB000222','OB000055' UNION ALL

    SELECT 'OB000224','OB000055' UNION ALL

    SELECT 'OB000225','OB000055' UNION ALL

    SELECT 'OB000226','OB000055' UNION ALL

    SELECT 'OB000227','OB000055' UNION ALL

    SELECT 'OB000228','OB000055' UNION ALL

    SELECT 'OB000230','OB000055' UNION ALL

    SELECT 'OB000231','OB000055' UNION ALL

    SELECT 'OB000232','OB000055' UNION ALL

    SELECT 'OB000234','OB000055' UNION ALL

    SELECT 'OB000235','OB000055' UNION ALL

    SELECT 'OB000236','OB000055' UNION ALL

    SELECT 'OB000237','OB000055' UNION ALL

    SELECT 'OB000239','OB000055' UNION ALL

    SELECT 'OB000241','OB000055' UNION ALL

    SELECT 'OB000242','OB000055' UNION ALL

    SELECT 'OB000244','OB000055' UNION ALL

    SELECT 'OB000245','OB000055' UNION ALL

    SELECT 'OB000151','OB000056' UNION ALL

    SELECT 'OB000247','OB000056' UNION ALL

    SELECT 'OB000248','OB000056' UNION ALL

    SELECT 'OB000250','OB000056' UNION ALL

    SELECT 'OB000251','OB000056' UNION ALL

    SELECT 'OB000254','OB000056' UNION ALL

    SELECT 'OB000256','OB000056' UNION ALL

    SELECT 'OB000060','OB000059' UNION ALL

    SELECT 'OB000069','OB000068' UNION ALL

    SELECT 'OB000072','OB000071' UNION ALL

    SELECT 'OB000075','OB000074' UNION ALL

    SELECT 'OB000097','OB000074' UNION ALL

    SELECT 'OB000098','OB000074' UNION ALL

    SELECT 'OB000263','OB000074' UNION ALL

    SELECT 'OB000264','OB000076' UNION ALL

    SELECT 'OB000078','OB000077' UNION ALL

    SELECT 'OB000083','OB000082' UNION ALL

    SELECT 'OB000085','OB000084' UNION ALL

    SELECT 'OB000088','OB000087' UNION ALL

    SELECT 'OB000090','OB000089' UNION ALL

    SELECT 'OB000092','OB000091' UNION ALL

    SELECT 'OB000094','OB000093' UNION ALL

    SELECT 'OB000096','OB000095' UNION ALL

    SELECT 'OB000117','OB000099' UNION ALL

    SELECT 'OB000121','OB000099' UNION ALL

    SELECT 'OB000123','OB000099' UNION ALL

    SELECT 'OB000126','OB000099' UNION ALL

    SELECT 'OB000127','OB000099' UNION ALL

    SELECT 'OB000128','OB000099' UNION ALL

    SELECT 'OB000130','OB000099' UNION ALL

    SELECT 'OB000132','OB000099' UNION ALL

    SELECT 'OB000134','OB000099' UNION ALL

    SELECT 'OB000135','OB000099' UNION ALL

    SELECT 'OB000137','OB000099' UNION ALL

    SELECT 'OB000138','OB000099' UNION ALL

    SELECT 'OB000140','OB000099' UNION ALL

    SELECT 'OB000142','OB000099' UNION ALL

    SELECT 'OB000144','OB000099' UNION ALL

    SELECT 'OB000147','OB000099' UNION ALL

    SELECT 'OB000149','OB000099' UNION ALL

    SELECT 'OB000066','OB000100' UNION ALL

    SELECT 'OB000107','OB000100' UNION ALL

    SELECT 'OB000109','OB000100' UNION ALL

    SELECT 'OB000110','OB000100' UNION ALL

    SELECT 'OB000111','OB000100' UNION ALL

    SELECT 'OB000113','OB000100' UNION ALL

    SELECT 'OB000115','OB000100' UNION ALL

    SELECT 'OB000116','OB000100' UNION ALL

    SELECT 'OB000145','OB000100' UNION ALL

    SELECT 'OB000146','OB000100' UNION ALL

    SELECT 'OB000260','OB000100' UNION ALL

    SELECT 'OB000262','OB000100' UNION ALL

    SELECT 'OB000074','OB000101' UNION ALL

    SELECT 'OB000105','OB000101' UNION ALL

    SELECT 'OB000106','OB000101' UNION ALL

    SELECT 'OB000058','OB000102' UNION ALL

    SELECT 'OB000152','OB000102' UNION ALL

    SELECT 'OB000253','OB000102' UNION ALL

    SELECT 'OB000057','OB000103' UNION ALL

    SELECT 'OB000124','OB000104' UNION ALL

    SELECT 'OB000108','OB000107' UNION ALL

    SELECT 'OB000112','OB000111' UNION ALL

    SELECT 'OB000114','OB000113' UNION ALL

    SELECT 'OB000118','OB000117' UNION ALL

    SELECT 'OB000120','OB000119' UNION ALL

    SELECT 'OB000153','OB000125' UNION ALL

    SELECT 'OB000129','OB000128' UNION ALL

    SELECT 'OB000131','OB000130' UNION ALL

    SELECT 'OB000133','OB000132' UNION ALL

    SELECT 'OB000136','OB000135' UNION ALL

    SELECT 'OB000139','OB000138' UNION ALL

    SELECT 'OB000141','OB000140' UNION ALL

    SELECT 'OB000143','OB000142' UNION ALL

    SELECT 'OB000148','OB000147' UNION ALL

    SELECT 'OB000150','OB000149' UNION ALL

    SELECT 'OB000154','OB000152' UNION ALL

    SELECT 'OB000157','OB000156' UNION ALL

    SELECT 'OB000159','OB000158' UNION ALL

    SELECT 'OB000161','OB000160' UNION ALL

    SELECT 'OB000164','OB000163' UNION ALL

    SELECT 'OB000167','OB000166' UNION ALL

    SELECT 'OB000170','OB000169' UNION ALL

    SELECT 'OB000172','OB000171' UNION ALL

    SELECT 'OB000177','OB000176' UNION ALL

    SELECT 'OB000179','OB000178' UNION ALL

    SELECT 'OB000181','OB000180' UNION ALL

    SELECT 'OB000183','OB000182' UNION ALL

    SELECT 'OB000185','OB000184' UNION ALL

    SELECT 'OB000194','OB000193' UNION ALL

    SELECT 'OB000196','OB000195' UNION ALL

    SELECT 'OB000199','OB000198' UNION ALL

    SELECT 'OB000202','OB000201' UNION ALL

    SELECT 'OB000204','OB000203' UNION ALL

    SELECT 'OB000207','OB000206' UNION ALL

    SELECT 'OB000210','OB000209' UNION ALL

    SELECT 'OB000213','OB000212' UNION ALL

    SELECT 'OB000215','OB000214' UNION ALL

    SELECT 'OB000219','OB000218' UNION ALL

    SELECT 'OB000221','OB000220' UNION ALL

    SELECT 'OB000223','OB000222' UNION ALL

    SELECT 'OB000229','OB000228' UNION ALL

    SELECT 'OB000233','OB000232' UNION ALL

    SELECT 'OB000238','OB000237' UNION ALL

    SELECT 'OB000240','OB000239' UNION ALL

    SELECT 'OB000243','OB000242' UNION ALL

    SELECT 'OB000246','OB000245' UNION ALL

    SELECT 'OB000249','OB000248' UNION ALL

    SELECT 'OB000252','OB000251' UNION ALL

    SELECT 'OB000255','OB000254' UNION ALL

    SELECT 'OB000261','OB000260' UNION ALL

    SELECT 'OB000266','OB000265' UNION ALL

    SELECT 'OB000269','OB000268' UNION ALL

    SELECT 'OB000271','OB000270' UNION ALL

    SELECT 'OB000273','OB000272' UNION ALL

    SELECT 'OB000033','OB000274' UNION ALL

    SELECT 'OB000034','OB000274' UNION ALL

    SELECT 'OB000035','OB000274' UNION ALL

    SELECT 'OB000036','OB000274' UNION ALL

    SELECT 'OB000276','OB000275' UNION ALL

    SELECT 'OB000277','OB000276' UNION ALL

    SELECT 'OB000281','OB000276' UNION ALL

    SELECT 'OB000278','OB000277' UNION ALL

    SELECT 'OB000279','OB000277' UNION ALL

    SELECT 'OB000280','OB000277' UNION ALL

    SELECT 'OB000282','OB000281' UNION ALL

    SELECT 'OB000283','OB000281'

    I need to find ALL child records for this set. That is e.g. OB000274 is one of the top records. I need a child not only for the first level below this, but also below the first level, stating OB000274 as parent. I know I can do cursor, but I would like a CTE.

    Here's the CTE so far:

    WITH eqstruct(eq_nmr, eq_child)

    AS(

    SELECT e.eq_parent, e.eq_nmr

    FROM mcmain.eqmas e

    WHERE Rtrim(IsNull(eq_parent,''))=''

    UNION ALL

    SELECT e.eq_parent, e.eq_nmr

    FROM mcmain.eqmas e

    INNER JOIN eqstruct d on e.eq_parent = d.eq_child

    )

    select * from eqstruct

    order by 1 desc

    But this gives me only the first level childs

    I would like to get:

    eq_nmr eq_child

    OB000274 OB000165

    OB000274 OB000037

    OB000274 OB000033

    ....

    OB000033 OB000165

    OB000033 OB000037

    ....

    OB000037 OB000165

    Any help, insights?

  • August 27, 2008 at 6:44 am

    #863676

    hi check the below query given

    the query will gives u all the childs corresponding to the parent and the level also.

    WITH eqstruct (level, eq_nmr , eq_parent ) AS

    (SELECT 1, ROOT.eq_parent, ROOT.eq_nmr

    FROM eqmas ROOT

    WHERE ROOT.eq_parent ='' and eq_nmr='OB000274'

    UNION ALL

    SELECT p.level+1, CHILD.eq_parent, CHILD.eq_nmr

    FROM eqstruct P, eqmas CHILD

    WHERE P.eq_parent = CHILD.eq_parent

    )

    SELECT eqstruct.eq_parent as eq_nmr, eqstruct.level, eqstruct.eq_nmr AS eq_parent

    FROM eqstruct

  • August 27, 2008 at 6:50 am

    #863680

    Thanks, but either I was unclear, or I don't understand the solution. I get :

    OB000033 2OB000274

    OB000034 2OB000274

    OB000035 2OB000274

    OB000036 2OB000274

    and

    OB000037 3OB000033

    OB000038 3OB000033

    OB000039 3OB000033

    OB000040 3OB000033

    OB000041 3OB000033

    So I'm missing OB000037 ? OB000274

    Maybe I should have asked for ALL the parents of the records?

    Cees

  • August 27, 2008 at 7:35 am

    #863727

    Explanation:

    Top Most Parent Childs level

    OB000274 OB000033 2

    OB000034 2

    OB000035 2

    OB000036 2

    These Childs Acts as parent in the Next Level

    Parent Child level

    OB000033 OB000037 3

    parent for the OB000037 is OB000033.

    But Ultimately the Top most parent for the OB000037 is OB000274

  • August 27, 2008 at 8:02 am

    #863754

    vkoka (8/27/2008)

    Explanation:

    Top Most Parent Childs level

    OB000274 OB000033 2

    OB000034 2

    OB000035 2

    OB000036 2

    These Childs Acts as parent in the Next Level

    Parent Child level

    OB000033 OB000037 3

    parent for the OB000037 is OB000033.

    But Ultimately the Top most parent for the OB000037 is OB000274

    I know 😉 but I'm trying to get it in the structure stated as above to insert it in a real eqstruct table. This table I can then use as a starting point for reports and queries.

    Here's what I have as a Cursor solution (RBAR :sick:: )

    Declare @Eqstruct Table(eqparent VarChar(20), eqchild VarChar(20)) -- Memory table om parent child relatie te berekenen

    Declare Dataset Cursor For Select eq_nmr from mcmain.eqmas

    Declare @Eqparent VarChar(20)

    Declare @Eqchild VarChar(20) -- wijzigt niet

    Declare @Eqnummer VarChar(20)

    OPEN Dataset

    FETCH NEXT FROM Dataset INTO @Eqnummer

    Set @Eqchild = @Eqnummer

    Set @Eqparent = (Select eq_parent from mcmain.eqmas where eq_nmr = @Eqnummer)

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    While Rtrim(@Eqparent) > ''

    Begin

    INSERT INTO @eqstruct(eqparent, eqchild)

    Values(@eqparent, @eqchild)

    -- Print @Eqparent+' '+@Eqchild+' '+@Eqnummer

    Set @EqNummer = @Eqparent

    Set @Eqparent = (Select eq_parent from mcmain.eqmas where eq_nmr = @Eqnummer)

    End

    FETCH NEXT FROM Dataset INTO @Eqnummer

    Set @Eqchild = @Eqnummer

    Set @Eqparent = @Eqnummer

    END

    Close Dataset

    Deallocate Dataset

    select * from @eqstruct

    where eqparent = 'OB000274'

Viewing 5 posts - 1 through 4 (of 4 total)

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