Recursive query or not

• dtopicdragovic

  Old Hand

  Points: 335

  More actions

  November 15, 2011 at 11:57 am

  #247815

  Hi,

  I have tables that look something like this:

  Document table

  DocID.......DocTitle

  232.......TitleA

  233.......TitleB

  241.......TitleC

  Doc_Version table

  ID...DocID...OldVersionID

  1.....233...........230

  2.....233...........232

  3.....232...........241

  What I am trying to do is when given search criteria is OldVersionID = 241 I want to get following DocIDs:

  232

  233

  I started with recursion query but I got error:

  "..statement terminated. The maximum recursion 100 has been exhausted ..."

  And here is my query:

  WITH Versions (OldVersionID, DocID, DocTitle, Level)

  AS

  (

  -- Anchor member definition

  SELECT dv.OldVersionID, e.DocID, e.DocTitle,

  0 AS Level

  FROM dbo.Document AS e

  INNER JOIN Doc_Version AS dv

  ON e.DocID = dv.DocID

  WHERE dv.OldVersionID = '241'

  UNION ALL

  -- Recursive member definition

  SELECT dv.OldVersionID, e.DocID, e.DocTitle,

  Level + 1

  FROM dbo.Document AS e

  INNER JOIN Doc_Version AS dv

  ON e.DocID = dv.DocID

  INNER JOIN Versions AS d

  ON dv.OldVersionID = d.DocID

  )

  SELECT OldVersionID, DocID, DocTitle, Level

  FROM Versions

  Any suggestions?

  Thanks

• dtopicdragovic

  Old Hand

  Points: 335

  More actions

  November 16, 2011 at 6:20 am

  #1408438

  Thanks for the reply. I am sorry but I am not sure that I understand your answer. Could you please give me more details? Am I at the right track at all?

  Thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 16, 2011 at 7:12 am

  #1408483

  DROP TABLE #Doc_Version

  CREATE TABLE #Doc_Version (ID INT,DocID INT, OldVersionID INT)

  INSERT INTO #Doc_Version (ID, DocID, OldVersionID)

  SELECT 1, 233, 230 UNION ALL

  SELECT 2, 233, 232 UNION ALL

  SELECT 3, 232, 241

  DECLARE @OldVersionID INT

  SET @OldVersionID = 241

  ;WITH rCTE AS (

  SELECT

  N = 1,

  DocID,

  OldVersionID

  FROM #Doc_Version

  WHERE OldVersionID = @OldVersionID

  UNION ALL

  SELECT

  N = lr.N+1,

  tr.DocID,

  tr.OldVersionID

  FROM rCTE lr

  INNER JOIN #Doc_Version tr ON tr.OldVersionID = lr.DocID

  )

  SELECT N, DocID

  FROM rCTE

  OPTION (MAXRECURSION 0);

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden