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

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