CTE for recursive querying

Question

I'm trying to query hierarchical data, using CTE's but I'm stuck on the following situation. I have the following data in my database:

dbo.ProjectComponents

Id
207

dbo.ProjectAccessories

Id  ProjectComponentId  AccessorySetId
4   34                  NULL
5   145                 NULL
6   207                 NULL
8   NULL                3

dbo.ProjectAccessorySets

Id  AccessoryId
1   NULL
2   NULL
3   6

I want to query all the dbo.ProjectAccessory.Id for a ProjectComponent.

The following query throws this error: Error 530: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

WITH AccessoryIds AS
(SELECT parent.Id, accessorySets.AccessoryId
FROM dbo.ProjectAccessories AS parent, dbo.ProjectAccessorySets AS accessorySets
WHERE parent.ProjectComponentId = 207 AND accessorySets.AccessoryId = parent.Id
UNION ALL
SELECT child.Id, childAccessorySets.AccessoryId
FROM dbo.ProjectAccessories AS child, dbo.ProjectAccessorySets AS childAccessorySets
INNER JOIN AccessoryIds AS asi ON childAccessorySets.AccessoryId = asi.Id
WHERE childAccessorySets.AccessoryId = child.Id AND childAccessorySets.AccessoryId IS NOT NULL)
SELECT * FROM AccessoryIds

Expected output

Id  AccessoryId
6   NULL
8   6

EDIT

Adding option (maxrecursion 0) did not solve the issue and made de query run forever. Next to that, the recursion depth in my database is only 2 at the moment (see added data)

Possible duplicate of [The maximum recursion 100 has been exhausted before statement completion](https://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion) — Circle Hsiao, Feb 09 '18 at 07:43
Did not resolve the issue. See the edited post for more info — JelleKerkstra, Feb 09 '18 at 07:58

HoneyBadger · Accepted Answer · 2018-02-09T09:23:02.263

Looks like this will do it:

DECLARE @ProjectAccessories TABLE
(
    Id INT, ProjectComponentId INT, AccessorySetId INT 
)
DECLARE @ProjectAccessorySets TABLE
(
    Id INT, AccessoryId INT
)

INSERT @ProjectAccessories (Id, ProjectComponentId, AccessorySetId)
VALUES (4, 34, NULL)
,       (5, 145, NULL)
,       (6, 207, NULL)
,       (8, NULL, 3)

INSERT @ProjectAccessorySets (Id, AccessoryId)
VALUES (1, NULL)
,       (2, NULL)
,       (3, 6) 

;
WITH AccessoryIds AS
(
        SELECT      parent.Id
        ,           parent.AccessorySetId AS AccessoryId
        FROM        @ProjectAccessories AS parent
        INNER JOIN  @ProjectAccessorySets AS accessorySets
                ON  accessorySets.AccessoryId = parent.Id
        WHERE       parent.ProjectComponentId = 207

        UNION ALL

        SELECT      child.id
        ,           childAccessorySets.AccessoryId
        FROM        AccessoryIds AS asi 
        INNER JOIN  @ProjectAccessorySets AS childAccessorySets
                ON  childAccessorySets.AccessoryId = asi.Id
        INNER JOIN  @ProjectAccessories AS child
                ON  child.AccessorySetId = childAccessorySets.id
)

SELECT  * 
FROM    AccessoryIds

Results in:

+----+-------------+
| Id | AccessoryId |
+----+-------------+
|  6 | NULL        |
|  8 | 6           |
+----+-------------+

CTE for recursive querying

1 Answers1