hierarchical question - grabbing everything related

hello,

thanks to some of you i learned how to perform hierarchical queries, via CONNECT BY PRIOR. very cool. it seems adept at sorting an entire table of hierarchical data.

but what i need now is a little different... i only need a subset of my "Parts" table, based on user-supplied name. but the app i bind this to needs *all* parents to be present in the result. meaning, in addition to retrieving the users filter matches, it needs to retrieve all ParentID rows and "treewalk" them too, and any/all of those nodes, etc...

not sure if i am clear. basically i need a subset of hierarchical data, but it needs to completely treewalk each row so that there are no orphaned references.

has anyone seen such a thing?

heres what im starting with, which is incomplete...

SELECT
    LEVEL,
    ObjectID,
    Name,
    ParentID

FROM
    tbl_Parts

--a good start, but doesnt bring back rows that are parents to found rows
START WITH (UPPER(Name) LIKE UPPER('test%')) CONNECT BY PRIOR ObjectID = ParentID

ORDER SIBLINGS BY ObjectID;

...thanks,
sm Received on Mon Nov 05 2007 - 14:29:42 CST