Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE rules_table (parent_rule, parent_key, child_rule, child_key) AS
SELECT 1, 1, 2, 2 FROM DUAL UNION ALL
SELECT 2, 2, 4, 4 FROM DUAL UNION ALL
SELECT 3, 3, 4, 4 FROM DUAL UNION ALL
SELECT 5, 5, 6, 6 FROM DUAL;
4 rows affected

CREATE TABLE status_table (rule, key, status) AS
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 2, 2, 0 FROM DUAL UNION ALL
SELECT 3, 3, 0 FROM DUAL UNION ALL
SELECT 4, 4, 1 FROM DUAL UNION ALL
SELECT 5, 5, 1 FROM DUAL UNION ALL
SELECT 6, 6, 1 FROM DUAL;
6 rows affected

SELECT CONNECT_BY_ROOT c.rule AS rule,
       CONNECT_BY_ROOT c.key AS key,
       MIN(c.status) AS status
FROM   status_table c
       LEFT OUTER JOIN rules_table r
       ON (c.rule = r.child_rule AND c.key = r.child_key)
CONNECT BY
       PRIOR r.parent_rule = c.rule
AND    PRIOR r.parent_key  = c.key
GROUP BY
       CONNECT_BY_ROOT c.rule,
       CONNECT_BY_ROOT c.key;


RULE
KEY
STATUS


1
1
1

2
2
0

3
3
0

4
4
0

5
5
1

6
6
1