Recursive Table Query Help Needed .....Sorely

Vmusic · #1 (**permalink**) 12-14-04, 18:08

Hi,
I have a recursive table and I need help with a query. The table is table of people and organizations. Sorry for the alignment but a sample is below:

party_id, party_type, first_nm, last_nm, org_nm, parent_id

22, ORGANIZATION, NULL, NULL, Smart Company, NULL (No parent)
45, PERSON, Jimmy, Smith, NULL, 22 (The parent here is party id 22)
59, PERSON, Johnny, BGood, NULL, 22 (The parent here is party id 22)

I want to write a query that retrieves a row and all its children through a search.

So in my example above, If Smart Company had ONLY 2 children rows, as shown my query would retrieve ALL 3 rows

The query will run as a search so the filter clause will contain WHERE org_nm LIKE '%Smart Co%' or whatever organization they're searching on.

This isn't for your faint of heart here......
THANKS!!!!
Vmusic

r937 · #2 (**permalink**) 12-14-04, 18:49

when you say "a row and all its children" do you mean just those which are one level down in the hierarchy?

Code:

select org.party_id
     , org.party_type
     , org.first_nm
     , org.last_nm
     , org.org_nm
     , org.parent_id
  from yourtable as org
 where org.org_nm like '%Smart Co%'   
union all
select per.party_id
     , per.party_type
     , per.first_nm
     , per.last_nm
     , per.per_nm
     , per.parent_id
  from yourtable as org
inner
  join yourtable as per
    on org.party_id
     = per.parent_id  
 where org.org_nm like '%Smart Co%'

Internet Services

Web Development

Digital Marketing

Consumer Tech