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

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

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

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