SQL hierarchy boolean roll up-CodePudding

I have two tables: Data and Data2. Data has 3 boolean values and Data2 is a link table to show the hierarchy of the data class. I'm trying to find a query which only shows the roots of Data2, and if any value in that tree has each of the boolean values as true.

Data Table

Name	Atrue	Btrue	Ctrue
parent1
parent2
child1	X	X
child2
child3		X	X

Data2 Table

parent_id	child_id
parent1	child1
parent2	child2
child2	child3

So in theory I would show

Name	Atrue	Btrue	Ctrue
parent1	1	1
parent2		1	1

I don't need the count of how many boolens are true, and just saying if any child has a true value, but I can still work with a count if it's easier.

CodePudding user response：

After joining your relationship table to your table of booleans, you can use CONNECT BY to tie the parents to the children. You can also use CONNECT_BY_ROOT and a NOT EXISTS statement to only get top level parents instead of every child in the entire relationship.

Query

WITH
    data1 (name,
           atrue,
           btrue,
           ctrue)
    AS
        (SELECT 'parent1', NULL, NULL, NULL FROM DUAL
         UNION ALL
         SELECT 'parent2', NULL, NULL, NULL FROM DUAL
         UNION ALL
         SELECT 'child1', 'X', 'X', NULL FROM DUAL
         UNION ALL
         SELECT 'child2', NULL, NULL, NULL FROM DUAL
         UNION ALL
         SELECT 'child3', NULL, 'X', 'X' FROM DUAL),
    data2 (parent_id, child_id)
    AS
        (SELECT 'parent1', 'child1' FROM DUAL
         UNION ALL
         SELECT 'parent2', 'child2' FROM DUAL
         UNION ALL
         SELECT 'child2', 'child3' FROM DUAL)
  SELECT parent_name,
         MAX (atrue)     AS atrue,
         MAX (btrue)     AS btrue,
         MAX (ctrue)     AS ctrue
    FROM (    SELECT CONNECT_BY_ROOT d2.parent_id     AS parent_name,
                     d1.atrue,
                     d1.btrue,
                     d1.ctrue
                FROM data2 d2 JOIN data1 d1 ON d2.child_id = d1.name
          CONNECT BY d2.parent_id = PRIOR d2.child_id)
   WHERE NOT EXISTS
             (SELECT 1
                FROM data2
               WHERE child_id = parent_name)
GROUP BY parent_name
ORDER BY parent_name;

Result

   PARENT_NAME    ATRUE    BTRUE    CTRUE
______________ ________ ________ ________
parent1        X        X
parent2                 X        X

CodePudding user response：

Something like this:

with
  h (name, descendant) as (
    select  connect_by_root(parent_id) as name, child_id
    from    data2
    start   with parent_id like 'parent%'  --  adapt as needed
    connect by   parent_id = prior child_id
  )
select h.name, max(d.atrue) as atrue, max(d.btrue) as btrue, max(d.ctrue) as ctrue
from   h left outer join data d on h.descendant = d.name
group  by h.name
;

NAME     ATRUE  BTRUE  CTRUE
-------  -----  -----  -----
parent2         X      X    
parent1  X      X

First run a straight hierarchical query ("connect by") and mark the root ("parent") for each child. Then join to the truth table, and aggregate over roots ("parents"). I used a left outer join for the possibility that a child in the hierarchical table doesn't appear at all in the truth table (interpreting that as "all columns are null for that child). In the hierarchical query, in the start with clause, I assumed the root names begin with 'parent' - that is not a good practice, a better one would be to have rows where the "parent" appears as child, with their parent shown as null. (You may also start with parent_id not in (select child_id from data2) - this will cause data2 to be scanned twice.)

CodePudding user response：

You can use a hierarchical query that starts with the valid children and work back to the roots (which, reversing the path through the hierarchy would be the leaves):

SELECT d.name,
       MAX(CONNECT_BY_ROOT atrue) AS Atrue,
       MAX(CONNECT_BY_ROOT btrue) AS Btrue,
       MAX(CONNECT_BY_ROOT ctrue) AS Ctrue
FROM   data d
       LEFT OUTER JOIN Data2 r
       ON (d.name = r.parent_id)
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       Atrue IS NOT NULL
OR     Btrue IS NOT NULL
OR     Ctrue IS NOT NULL
CONNECT BY PRIOR d.name = r.child_id
GROUP BY d.name

Which, for the sample data:

CREATE TABLE Data (Name, Atrue, Btrue, Ctrue) AS
SELECT 'parent1', NULL, NULL, NULL FROM DUAL UNION ALL          
SELECT 'parent2', NULL, NULL, NULL FROM DUAL UNION ALL          
SELECT 'parent3', NULL, NULL, NULL FROM DUAL UNION ALL          
SELECT 'parent4', NULL, NULL, NULL FROM DUAL UNION ALL          
SELECT 'parent5', NULL, NULL, NULL FROM DUAL UNION ALL          
SELECT 'child1',  'X',  'X',  NULL FROM DUAL UNION ALL
SELECT 'child2',  NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 'child3',  NULL, 'X',  'X'  FROM DUAL UNION ALL
SELECT 'child4',  NULL, NULL, 'X'  FROM DUAL UNION ALL
SELECT 'child5',  NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 'child6',  NULL, 'X',  NULL FROM DUAL UNION ALL
SELECT 'child7',  NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 'child8',  NULL, NULL, NULL FROM DUAL;

CREATE TABLE Data2 (parent_id, child_id) AS
SELECT 'parent1', 'child1' FROM DUAL UNION ALL
SELECT 'parent2', 'child2' FROM DUAL UNION ALL
SELECT 'parent3', 'child4' FROM DUAL UNION ALL
SELECT 'parent3', 'child5' FROM DUAL UNION ALL
SELECT 'parent5', 'child7' FROM DUAL UNION ALL
SELECT 'child2',  'child3' FROM DUAL UNION ALL
SELECT 'child5',  'child6' FROM DUAL UNION ALL
SELECT 'child7',  'child8' FROM DUAL;

Outputs:

NAME ATRUE BTRUE CTRUE

parent2 null X X

parent1 X X null

parent3 null X X

NAME	ATRUE	BTRUE	CTRUE
parent2	null	X	X
parent1	X	X	null
parent3	null	X	X

db<>fiddle here