recursive cte

• siraken

  SSC Veteran

  Points: 258

  More actions

  December 22, 2010 at 7:17 am

  #232699

  Hi!

  Im having some problem with recursive cte. I want to find all children of the parent. For example I have a table with two columns: parentId and childrenID:

  pid id

  1 2

  1 3

  1 4

  2 4

  2 5

  3 1

  3 2

  4 5

  as a result I want to get all children of each parent:

  pid id

  1 1

  1 2

  1 3

  1 4

  1 5

  2 4

  2 5

  3 1

  3 2

  3 3

  3 4

  3 5

  4 5

  All my attempts were unsuccessful. I couldnt get result as I wanted. Help me pls to write a query to find all children.

• Paul White

  SSC Guru

  Points: 150468

  More actions

  December 22, 2010 at 7:24 am

  #1265514

  Help us to help you:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Existing SSC articles that you may find helpful:

  http://www.sqlservercentral.com/search/?q=hierarchy&t=a

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• siraken

  SSC Veteran

  Points: 258

  More actions

  December 22, 2010 at 7:39 am

  #1265522

  sry...given:

  with cte as (

  select 1 as pid, 2 as id union all

  select 1,3 union all

  select 1,4 union all

  select 2,4 union all

  select 2,5 union all

  select 3,1 union all

  select 3,2 union all

  select 4,5

  )

  Did I missed somethin else? Is it enough?