CODEWITHSUNDEEP
sqloracle11gtreehierarchical-data
redhead
redhead

Reputation: 1317

Oracle Hierarchical query with condition on the whole tree

I need, using the hierarchical (or other) query, to select tree-structured data where a certain condition must hold for the whole tree (ie. all the nodes in the tree).

That means that if a single node of a tree violates the condition, then the tree is not selected at all (not even other the nodes of that tree that do comply with the condition, so the complete tree is thrown away).

Also I want to select all trees - all the nodes of such trees where the condition holds for every node (ie. select not just one such tree but all such trees).

EDIT:

Consider this example of table of files that are connected to each other through parent_id column so they form trees. There is also a foreign key owner_id, which references other table primary key.

PK file_id | name | parent_id | owner_id
----------------------------------------
   1       | a1   | null      | null                 -- root of one tree
   2       | b1   | 1         | null
   3       | c1   | 1         | null
   4       | d1   | 2         | 100
   5       | a2   | null      | null                 -- root of another tree
   6       | b2   | 5         | null
   7       | c2   | 6         | null
   8       | d2   | 7         | null

Column parent_id has a foreign key constraint to file_id column (making the hierarchies).

And there is one more table (let's call it junction table) where (among others) the foreign keys file_ids are stored in many-to-one relation ship to the table of files above:

FK file_id | other data
-----------------------
   1       | ...
   1       | ...
   3       | ...

Now the query I need is to select all such whole trees of files where following conditions are met for each and every file in that tree:

For the example above, the query should result in:

file_id | name | parent_id | owner_id
---------------------------------------
5       | a2   | null      | null
6       | b2   | 1         | null
7       | c2   | 1         | null
8       | d2   | 2         | null

All nodes make a whole tree as it is in the table (no missing children or parents) and each of the nodes holds to the conditions above (has no owner and no relation in junction table).

Upvotes: 0

Views: 1384

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

This generates the tree with a simple hierarchical query - which is really only needed to establish the root file_id for each row - while joining to junction to check for a record there. That can get duplicates, which is OK at that stage. The analytic version of max() is then applied to the intermediate result set to determine whether your conditions are met for any row with the same root:

select file_id, name, parent_id, owner_id
from (
  select file_id, name, parent_id, owner_id,
    max(j_id) over (partition by root_id) as max_j_id,
    max(owner_id) over (partition by root_id) as max_o_id
  from (
    select f.*, j.file_id as j_id,
      connect_by_root f.file_id as root_id
    from files f
    left outer join junction j
    on j.file_id = f.file_id
    connect by prior f.file_id = f.parent_id
    start with f.parent_id is null
  )
)
where max_j_id is null
and max_o_id is null
order by file_id;

 FILE_ID   NAME   PARENT_ID   OWNER_ID 
--------- ------ ----------- ----------
        5     a2      (null)     (null) 
        6     b2           5     (null) 
        7     c2           6     (null) 
        8     d2           7     (null)

The innermost query gets the root and any matching junction records (with duplicates). The next level adds the analytic max owner and junction value (if there is one), giving the same result to every row for the same root. The outer query then filters out any rows which have either value for any row.

SQL Fiddle.

Upvotes: 1

Related Questions