Re: hierarchical question - grabbing everything related

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Tue, 06 Nov 2007 13:13:56 -0000
Message-ID: <1194354836.262080.28840@o80g2000hse.googlegroups.com>

On Nov 5, 3:29 pm, SpaceMarine <spacemar..._at_mailinator.com> wrote:
> 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

Hi,

Are you asking that if Steve is the parent, and Abigail and Madeline are the children, that you also want to see Abigail if Madeline is selected?

      Steve
   /           \
  /             \
  |              |

Abigail Madeline

Regards,

Steve Received on Tue Nov 06 2007 - 07:13:56 CST