Using SQL is there a way to compare if a node is in the parent hierarchy of any of a set of nodes?

Question

I need to search my hierarchy to return all nodes that are above or equal to any node returned in a sub-query.

given table: H

Child | Parent
  A1  | A
  A2  | A
  A1b | A1
  A2a | A2
  A2b | A2
  B1  | B
  B3  | B
  B1c | B1
  B3a | B3

Sub-query returns:

A2a
A2b
B3

I need a query that uses the results of the sub-query to return.

A
A2
A2a
A2b
B
B3

I've been trying to use the query from this SO questionto work it out, but no luck so far.

Answer 1

You could put your existing query in a CTE, and then set up the hierarchical query like:

WITH q(id) AS ( ... )
SELECT id FROM q
UNION ALL
SELECT DISTINCT Parent
FROM mytable
CONNECT BY PRIOR Parent = Child
START WITH Child in (SELECT id FROM q)
ORDER BY id

Demo on DB Fiddle with your sample data:

WITH q(id) AS (
    SELECT 'A2a' FROM DUAL
    UNION ALL SELECT 'A2b' FROM DUAL
    UNION ALL SELECT 'B3' FROM DUAL
)
SELECT id FROM q
UNION ALL
SELECT DISTINCT Parent
FROM mytable
CONNECT BY PRIOR Parent = Child
START WITH Child in (SELECT id FROM q)
ORDER BY id

| ID  |
| :-- |
| A   |
| A2  |
| A2a |
| A2b |
| B   |
| B3  |