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