Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    54

    Unanswered: Help with query - Using 4.0.22 MySQL

    Hi,
    I have a table that stores people and organizations in one table. The table is called 'party'.

    There is a recursive relationship designed such that the table key is 'party_id' and there is also a column called 'parent_id'.

    WITHOUT using sub select, I need help writing a query that brings back all rows and their parent, if there is one.

    So for example a row with party_id 22 is Billy Bob and the value for his 'parent_id' might be 33. Row with party_id 33 would be General Motors which is who Billy Works for.

    Here's the example (I hope) laid out in row
    PARTY_ID | LAST_NM | FIRST_NM | ORG_NM | PARENT_ID | ETC.
    22 | Bob | Billy | NULL | 33 | etc.
    33 | NULL | NULL | G.M. | NULL | etc.
    44 | Susie | Smith | NULL | NULL | etc.



    Can someone help write a query that returns all rows only once but will include a column with the parent org_nm as the correct parent?

    The result row for Bill Bob would show the GM is his 'parent' in the table.

    Thanks In Advance!!!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That is a very, very, VERY odd design choice for this situation.

    Have you considered putting your "parent" companies in a seperate table? That's kind of what rdbms's are for...

    If you were to create a table of all the org's and temporarily retain their party_id, you could then update all of your party's to the new parent_id and not have to deal with this kind of wackiness.

    So basically you would have:

    ORG
    --------
    org_id
    name
    party_id *(this field would be deleted after you've properly setup your party table)

    PARTY
    --------
    party_id
    org_id
    last_nm
    first_nm
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, it isn't all that odd

    however, organizations and people sharing a "party" table would not have separate columns (in which case, yes, separate tables would be better), rather, they would make use of the same columns (so the org name might be in the lastname column, or else the first and last names would together be in a single name column), along with a "type" indicator to distinguish between people and orgs

    see Party Data Model for a more comprehensive design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2004
    Posts
    54

    Party Party Party

    Yo folks.. I design databases for a living. People and Organizations are abstractly 'parties' or even more abstractly 'role players'.

    I figured it out....

    Thanks

Posting Permissions

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