Recursive query with 2 variables and select on status

Question

I have a table with rules on an Oracle database:

parent_rule | parent_key |child_rule | child_key

and a second table with the status of the child

rule | key | status

All rules and keys are numbers.

I need a query that looks into every parent of a given child. It should return 1 if all parents of the child have status 1 and 0 if just one parent is having status 0. I found a hierarchical query but I don't know how to traverse the rules with 2 variables instead of one.

I tried a normal hierarchical query with just one variable like

SELECT
    child_rule, 
    CONNECT_BY_ROOT parent_rule, SYS_CONNECT_BY_PATH( parent_rule, '/'  ) 
FROM rules
CONNECT BY PRIOR parent_rule=child_rule;  

I don't know how to add the second variable. Also my data shows loops here due to the missing key.

The select I don't have any clue how to do it.

Answer 1

Parent Status

Your question does not contain any sample data or expected output; however, from your description, if you only want to consider parents (and not all ancestors or the child itself) then you do not appear to need a hierarchical query and can use a simple JOIN and then aggregate with conditional aggregation to check if there exists zero rows for each child with a status of 0 and, if so, output 1 for that child else output 0:

SELECT r.child_rule,
       r.child_key,
       MIN(s.status) 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

Which, for the sample data:

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;

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;

Outputs:

CHILD_RULE	CHILD_KEY	STATUS
2	2	1
4	4	0

fiddle

---

Ancestor Status

If you do want ancestors (which the question does not mention) and you want to consider the status of the child and all its ancestors then you can use:

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;

Which, for the sample data:

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;

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;

Outputs:

RULE	KEY	STATUS
1	1	1
2	2	0
3	3	0
4	4	0
5	5	1
6	6	1

fiddle