CTE Recursive Query to Grandparents

I have following table:

ID NAME PARENT_ID ISDELETED ISINEDIT
1  JJ     NULL     1          0
2  AR     1        0          0
3 PR      2        0          0
4 DR      NULL     0          1

What I need to get is SELECT query that will return only rows that have ISDELETED 0 and ISINEDIT 0 and whose parents or grandparents are 0 as well

I currently have:

;WITH ChildParent AS
(
    SELECT
        a.id,
        a.name,
        a.isinedit,
        a.parent_id,
        a.isdeleted
    FROM dbo.table 
    WHERE isdeleted = 0 AND isinedit = 0 

    UNION ALL

    SELECT
        a.id,
        a.name,
        a.isinedit,
        a.parent_id,
        a.isdeleted
    FROM dbo.table a
    INNER JOIN ChildParent cp ON a.parent_id = cp.id
    WHERE a.isdeleted = 0  AND a.isinedit = 0
)
SELECT  
        id,
        name,
    parent_id,
        isinedit,
        isdeleted
FROM ChildParent

But for some reason it returns double rows

Answer

You need to add the same isdeleted = 0 AND isinedit = 0 predicate to the INNER JOIN childParent CP source.

…but if you do that you make your CTE query very fiddly and if you have to repeat the same thing over-and-over there’s probably a better way to do it.

…and there is! A SELECT query can have multiple CTE expressions:

;
WITH filtered AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id,
    FROM
        dbo.Table
    WHERE
        IsDeleted = 0
        AND
        IsInEdit = 0 
)

WITH cte AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered

    UNION ALL

    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered
        INNER JOIN cte ON a.parent_id = cte.id
)
SELECT  
    *
FROM
    cte
ORDER BY
    id