Recursive CTE and how to overcome circular reference error

Question

I have a script that is meant to find the original Location. It starts with a table like this:

ID	PreviousID	Location
2	NULL	1235
3	2	1236
4	3	1239
8	11	1237
9	8	1234
10	9	1235
11	10	1237

I want to find the original Location for a select number of ID values

ID
2
4
8
10
11

The result I am looking for is:

lvl	ID	OriginalId	Location	OriginalLocation
0	2	2	1235	1235
2	4	2	1239	1235
0	8	8	1237	1237
2	10	8	1235	1237
3	11	8	1237	1237

The output for ID =2 and =4 is good. However, with 8,10,11, this result in circular reference and the code breaks.

Question: Is there away to address this issue so that the error does not occur:

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

and produce the desired outputs? I know 8 is the root since it is the smallest number in that chain even though there is circular reference in that chain.

Here is the script that I have so far to produce output for ID 2 and 4. It works if you remove value 8,10,11

DECLARE @IDs TABLE (
  ID INTEGER
  ,PreviousID INTEGER
  ,Location INTEGER
)

INSERT INTO @IDs
SELECT           2,null,1235
UNION ALL SELECT 3,2,1236
UNION ALL SELECT 4,3,1239
UNION ALL SELECT 8,11,1237
UNION ALL SELECT 9,8,1234
UNION ALL SELECT 10,9,1235
UNION ALL SELECT 11,10,1237

Select * from @IDs


DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('2')
,('4')
--,('8')
--,('10')
--,('11')

;WITH q AS (
    SELECT 0 lvl,  ID, PreviousID,PreviousID LastId
        ,Location,Location as OriginalLocation
    FROM    @IDs
    where ID in (select OrderID from @ORDERID) 
    UNION ALL 
    SELECT lvl+1, q.ID,u.PreviousId,q.PreviousId LastId
       ,q.Location,u.Location
    FROM    q
            INNER JOIN @IDs u ON u.ID = q.PreviousID
            --and q.ID <> u.PreviousID and q.PreviousID <> u.ID
)
select lvl, ID, coalesce(LastId,Id) OriginalId,Location,OriginalLocation 
from q
where PreviousId is null
order by id;

Thank you very much for any hint or suggestion.

Answer 1

You were mostly there... you just needed to make two changes:

1. Add an additional join condition q.ID > u.Id to stop when you get to the smallest Id.
2. Calculate which results to show to include those that had a recusion. I found it clearer to add a specific calculation to determine the root then to complicate the where clause logic.

WITH q AS (
    SELECT 0 lvl
        , ID
        , PreviousID
        , PreviousID LastId
        , Location
        , Location AS OriginalLocation
        , CONVERT(bit, CASE WHEN PreviousID IS NULL OR PreviousId > Id THEN 1 ELSE 0 END) IsRoot
    FROM @IDs
    WHERE ID in (select OrderID from @ORDERID) 
  
    UNION ALL 
  
    SELECT lvl + 1
        , q.ID
        , u.PreviousId
        , q.PreviousId LastId
        , q.Location
        , u.Location
        , CONVERT(bit, CASE WHEN u.PreviousID IS NULL OR u.PreviousId > u.Id THEN 1 ELSE 0 END) IsRoot
    FROM q
    INNER JOIN @IDs u ON u.ID = q.PreviousID
        -- Ensure we stop at the lowest Id
        AND q.ID > u.Id
)
SELECT lvl, ID, coalesce(LastId,Id) OriginalId, Location, OriginalLocation
FROM q
WHERE IsRoot = 1
ORDER BY id;

DBFiddle