Recursive query or not

Question

Recursive query or not

dtopicdragovic

Old Hand

Points: 365
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

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

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

dtopicdragovic Old Hand Points: 365 More actions · Answer 1

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 · Answer 2

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