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;
3 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;
3 rows affected

SELECT r.child_rule,
       r.child_key,
       CASE COUNT(CASE s.status WHEN 0 THEN 1 END)
       WHEN 0
       THEN 1
       ELSE 0
       END AS status
FROM   rules_table r
       INNER JOIN status_table s
       ON (s.rule = r.parent_rule AND s.key = r.parent_key)
GROUP BY
       r.child_rule,
       r.child_key


CHILD_RULE
CHILD_KEY
STATUS


2
2
1

4
4
0