Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004

    Unanswered: Recursive Table Query Help Needed .....Sorely

    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......

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    when you say "a row and all its children" do you mean just those which are one level down in the hierarchy?
    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
      join yourtable as per
        on org.party_id
         = per.parent_id  
     where org.org_nm like '%Smart Co%' | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts