Recursive Querry to get all Childs

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  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;

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  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.