Post reply

Recursive Querry to get all Childs

  • August 12, 2009 at 4:57 am

    #213451

    Hi ,

    I have a table which has parent child relation ship. I need to build a stored procedure which accepts a Parameter 'pid' and returns all the childs for that ParentID.

    [Code]

    declare @t1 table (id int,pid int,s varchar(100))

    insert into @t1 values (1,0,'Solid')

    insert into @t1 values (2,0,'Liquid')

    insert into @t1 values (3,0,'Gas')

    insert into @t1 values (4,1,'Brick')

    insert into @t1 values (5,1,'Stone')

    insert into @t1 values (6,2,'Water')

    insert into @t1 values (7,2,'Milk')

    insert into @t1 values (8,3,'Oxygen')

    insert into @t1 values (9,3,'Air')

    insert into @t1 values (10,6,'Hot Water')

    insert into @t1 values (11,6,'Cold Water')

    select * from @t1

    [/Code]

    Expected output if 'pid' passed is 2

    o/p

    --------

    Liquid

    Water

    Milk

    Hot Water

    Cold Water

    Thanks.

  • August 12, 2009 at 6:26 am

    #1037935

    Hope this gets you started in the right direction. All I did was mimic the sample code from BOL (Books Online).

    create table #t1 (id int,pid int,s varchar(100))

    insert into #t1 values (1,0,'Solid')

    insert into #t1 values (2,0,'Liquid')

    insert into #t1 values (3,0,'Gas')

    insert into #t1 values (4,1,'Brick')

    insert into #t1 values (5,1,'Stone')

    insert into #t1 values (6,2,'Water')

    insert into #t1 values (7,2,'Milk')

    insert into #t1 values (8,3,'Oxygen')

    insert into #t1 values (9,3,'Air')

    insert into #t1 values (10,6,'Hot Water')

    insert into #t1 values (11,6,'Cold Water');

    select * from #t1;

    with ChildRecs (id, pid, s) as (

    select id, pid, s

    from #t1

    where pid = 0 and id = 2

    union all

    select t.id, t.pid, t.s

    from #t1 t inner join ChildRecs cr on t.pid = cr.id

    )

    select s from ChildRecs;

    drop table #t1;

  • August 12, 2009 at 6:39 am

    #1037948

    Thanks :w00t: thats what i needed

    Is this magic of recursion ?

    I will be very thankful if you give a brief idea of that query.

    Thanks again.

  • August 12, 2009 at 7:55 am

    #1037991

    descentflower (8/12/2009)

    Thanks :w00t: thats what i needed

    Is this magic of recursion ?

    I will be very thankful if you give a brief idea of that query.

    Thanks again.

    Read about Recursive CTE's in BOL. That is a good place to start.

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

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