Problem with recursive query

• incognito_gbg

  Newbie

  Points: 1

  More actions

  September 16, 2008 at 2:29 am

  #190838

  I can´t find whats wrong with this recursive query:

  WITH temp (Id, ParentId, iteration)

  AS

  (

  SELECT Id, ParentId, 0 AS iteration

  FROM OrgTable WHERE ParentId IS Null

  UNION ALL

  SELECT a.Id, b.ParentId, a.iteration + 1

  FROM temp a

  JOIN OrgTable b

  on b.Id = a.ParentId

  )

  SELECT *

  FROM temp

  This just gives me the two records which are root objects. It doesnt give me any child rows at all.

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  September 16, 2008 at 2:46 am

  #872190

  I think it shud be..

  WITH temp (Id, ParentId, iteration)

  AS

  (

  SELECT Id, ParentId, 0 AS iteration

  FROM OrgTable WHERE ParentId IS Null

  UNION ALL

  SELECT a.Id, b.ParentId, a.iteration + 1

  FROM temp a

  JOIN OrgTable b

  on b.ParentId= a.id

  )

  i have changed b.Id = a.ParentId to b.ParentId = a.id

  "Keep Trying"