Find nth parent that matches some condition

Question

I have a table that has a structure similar to the following:

Id	Parent Id	Condition
1	0	0
2	1	0
3	0	0
4	3	0
5	3	0
6	3	1
7	6	0
8	6	1
9	8	0
10	9	0

where Condition is a bit that indicates whether the row meets a specific condition. in most cases the condition is 0, but sometimes it is 1.

I want to return the Id and the parent where condition is 0, which can be 1 or more levels above the immediate parent. if I just wanted immediate parent, it is already inside the table, but in the example above I would like to return a parent Id of 3 for Id 7, even though 7's immediate parent Id is 6, but 6 matches some condition so it's next parent would be 3. here is what I would like the results to look like:

Id	NonConditionParent
1	0
2	1
3	0
4	3
5	3
6	3
7	3
8	3
9	3
10	9

I created the following sql with a recursive CTE, but it just returns the immediate parent of the id, which the table already contains:

WITH NON_CONDITION_PARENT(Id,ParentId) AS
(
    SELECT M.Id, M.ParentId
    FROM [Mytable] M 

    UNION ALL
    
    SELECT M2.Id, M2.ParentId
    FROM [Mytable] as M2 
    inner join NON_CONDITION_PARENT on M2.ParentId=NON_CONDITION_PARENT.Id
    WHERE M2.Condition = 0
)
SELECT * FROM NON_CONDITION_PARENT

I'm not entirely sure a recursive CTE is the right thing for this, but I know recursion plays a part - can someone point me in the right direction? or help correct my CTE?

I thought about getting all the parents that match a condition then getting the min parent id for a group, but I still don't know how to get all parents for a single id and return it along with the fileid

Answer 1

You can try to create udf to get the NonConditionParent. Here is the sample code

CREATE FUNCTION GetNonConditionParent(@Id INT)
RETURNS INT
AS
BEGIN
    DECLARE @Parent INT;
    SELECT @Parent = ParentId
FROM MyTable
WHERE Id = @Id;

WHILE EXISTS (SELECT 1 FROM MyTable WHERE Id = @Parent AND Condition = 1)
BEGIN
    SELECT @Parent = ParentId
    FROM MyTable
    WHERE Id = @Parent;
END

RETURN @Parent;
END;

And then you can select with CTE like you have

WITH CTE AS (
    SELECT Id, ParentId, Condition
    FROM MyTable
    WHERE Condition = 0
    UNION ALL
    SELECT M.Id, M.ParentId, M.Condition
    FROM MyTable M
    INNER JOIN CTE ON M.Id = CTE.ParentId
)
SELECT DISTINCT Id, dbo.GetNonConditionParent(Id) AS NonConditionParent
FROM CTE
ORDER BY Id;

Or, you can simply select without CTE like this

SELECT DISTINCT Id, dbo.GetNonConditionParent(Id) AS NonConditionParent
FROM MyTable
ORDER BY Id;

Without distinct works too

SELECT  Id, dbo.GetNonConditionParent(Id) AS NonConditionParent
FROM MyTable
ORDER BY Id;

Here is the output (same output for all codes above):