Need an Oracle hierarchical query that returns only full trees for records where children match a search string

Question

Here's the full example data set for this query without any pruning of trees where no node matches the search string:

Level  parent     id     text
---------------------------------------------
0      0          1      toplevel
1      1          2      foo
1      1          3      sumthin else
1      1          4      foo
0      0          7      toplevel2
1      7          8      secondlevel
1      7          9      anothersecondlevel

I need to return the following if the user searches on 'foo':

0      0          1      toplevel
1      1          2      foo
1      1          4      foo

The real case is a bit more complex (i.e., three levels in the tree that I want to return) but this captures the issue. In English, return the ancestor tree for an node that matches the search string starting at the matching node on text column and return all ancestors.

I am new to Oracle (at least recently) and have tried adding to the CONNECT BY clause without any success - always returns the following:

1      1          2      foo
1      1          4      foo

PS - the oracle docs and examples on this imply that CONNECT_BY_ROOT will capture the ancestors but all it seems to do is return top level (ROOT) values.

Answer 1

Depending upon your use of the LEVEL column (as per my comment).

Info on Oracle Hierarchical Queries: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

This returns what you ask for if LEVEL is the Oracle pseudocolumn:

WITH t AS (SELECT 0 AS parent,
                  1 AS id,
                  'toplevel' AS text FROM DUAL
           UNION
           SELECT 1 AS parent,
                  2 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 1 AS parent,
                  3 AS id,
                  'sumthin else' AS text FROM DUAL
           UNION
           SELECT 1 AS parent,
                  4 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 0 AS parent,
                  7 AS id,
                  'toplevel2' AS text FROM DUAL
           UNION
           SELECT 7 AS parent,
                  8 AS id,
                  'secondlevel' AS text FROM DUAL
           UNION
           SELECT 7 AS parent,
                  9 AS id,
                  'anothersecondlevel' AS text FROM DUAL
          ) 
SELECT UNIQUE
       level,
       parent,
       id,
       text
  FROM t
 START WITH text = 'foo'
 CONNECT BY PRIOR parent = id
 ORDER BY parent;

Returns:

LEVEL PARENT ID TEXT
    2      0  1 toplevel
    1      1  2 foo     
    1      1  4 foo     

If LEVEL is a column in your table then:

WITH t AS (SELECT 0 AS tlevel,
                  0 AS parent,
                  1 AS id,
                  'toplevel' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  1 AS parent,
                  2 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  1 AS parent,
                  3 AS id,
                  'sumthin else' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  1 AS parent,
                  4 AS id,
                  'foo' AS text FROM DUAL
           UNION
           SELECT 0 AS tlevel,
                  0 AS parent,
                  7 AS id,
                  'toplevel2' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  7 AS parent,
                  8 AS id,
                  'secondlevel' AS text FROM DUAL
           UNION
           SELECT 1 AS tlevel,
                  7 AS parent,
                  9 AS id,
                  'anothersecondlevel' AS text FROM DUAL
          ) 
SELECT UNIQUE
       tlevel,
       parent,
       id,
       text
  FROM t
 START WITH text = 'foo'
 CONNECT BY PRIOR parent = id
 ORDER BY parent;

Returns:

TLEVEL PARENT ID TEXT
     0      0  1 toplevel
     1      1  2 foo     
     1      1  4 foo     

Hope it helps...

Answer 2

To traverse from the bottom up the important bit is the order of values after the CONNECT BY PRIOR) The order by is used to reverse the output (as the root is foo) and the distinct removes the duplicate toplevel values:

SELECT DISTINCT LEVEL, id, text
FROM t1
CONNECT BY PRIOR parent = id
START WITH text = 'foo'
ORDER BY LEVEL DESC

Note: if you add a child to foo and switch the CONNCT BY PRIOR id = parent you will get the children

if you want to see the whole hierarchy you could find the top, of the tree (by looking for the row with no parent)

SELECT id
FROM t1
WHERE parent=0
CONNECT BY PRIOR parent = id
START WITH text = 'foo'

then use this as the START WITH id (and reverse the order of the tree traversal in the outer query, id = parent) :

SELECT DISTINCT LEVEL, id, text
FROM t1
CONNECT BY PRIOR id = parent
START WITH id IN 
(
    SELECT id
    FROM t1
    WHERE parent=0
    CONNECT BY PRIOR parent = id
    START WITH text = 'foo'
)
ORDER BY LEVEL DESC

Answer 3

My take on "Oracle hierarchical query that returns only full trees" part would be:

SELECT Parent_ID    
     , Child_ID 
     , INITIAL_Parent_ID
     , child_level
     , INITIAL_Parent_ID || children_CHAIN AS CONNECTION_CHAIN
FROM ( SELECT CONNECT_BY_ROOT Parent_ID AS INITIAL_Parent_ID
            , Parent_ID
            , Child_ID
            , LEVEL AS child_level
            , SYS_CONNECT_BY_PATH(Child_ID, '/') AS children_chain
       FROM REF_DECOMMISSIONS
       CONNECT BY NOCYCLE Parent_ID = PRIOR Child_ID
     )
WHERE INITIAL_Parent_ID NOT IN (SELECT Child_ID FROM REF_DECOMMISSIONS)
;

To filter the trees to the ones containing the specific children you'd need to add another condition to the last WHERE to further filter INITIAL_Parent_ID. The query would become:

WITH ROOT_TREES AS
( SELECT Parent_ID  
       , Child_ID   
       , INITIAL_Parent_ID
       , child_level
       , INITIAL_Parent_ID || children_CHAIN AS CONNECTION_CHAIN
  FROM ( SELECT CONNECT_BY_ROOT Parent_ID AS INITIAL_Parent_ID
              , Parent_ID
              , Child_ID
              , LEVEL AS child_level
              , SYS_CONNECT_BY_PATH(Child_ID, '/') AS children_chain
         FROM REF_DECOMMISSIONS
         CONNECT BY NOCYCLE Parent_ID = PRIOR Child_ID
       )
  WHERE INITIAL_Parent_ID NOT IN (SELECT Child_ID FROM REF_DECOMMISSIONS)
)
SELECT * 
  FROM root_trees 
WHERE INITIAL_Parent_ID IN (SELECT INITIAL_Parent_ID 
                              FROM root_trees 
                             WHERE Child_ID = 123)
;