Recursive Query CTE Father - Son - Grandson error-CodePudding

I have a table that has an ID and IDFATHER of some projects, these projects can receive N sons, so, the structure is like

ID	IDFATHER	REV
1	1	0
2	1	1
5	2	2

I need to, iniciating in ID 5 go to ID 1, so I did a CTE Query:

WITH lb (ID, IDFATHER) AS (
    SELECT ID, IDFATHER
    FROM PROJECTS
    WHERE ID = 5

    UNION ALL

    SELECT I.ID, I.IDFATHER
    FROM PROJECTS I
    JOIN lb LBI ON I.ID = LBI.IDFATHER
    --WHERE I.ID = LBI.IDFATHER -- Recursive Subquery 
)
SELECT *
FROM lb
WHERE LB.ID = LB.IDFATHER

When this code runs it gives me:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

So basically I handle it by just adding:

SELECT TOP 1 * FROM LB WHERE  LB.ID = LB.IDFATHER

But I really want to know were is my error. Can anyone give me a hand on these?

CodePudding user response：

The first row points to itself so the recursion never stops. You need to add this condition inside the recursive cte:

WHERE LBI.ID <> LBI.IDFATHER

I would rather set IDFather of the first row to NULL.

CodePudding user response：

The recursion didn't stop because your top row refers to itself endlessly.

If the top row has a null parent, that would have stopped the recursion.

Another approach is to use that case id = parentid as the termination logic.