Came across an issue that stumped me today and wanted to see if anyone had some insight. Sorry for the bad formatting, not on here very much. I have 2 tables Organization and personOrganization
Table 1 lists all Organizations in the system, columns are
| OrgPK | ParentOrgPK | OrgName |
|---|---|---|
| 3 | 2 | Org 3 |
| 2 | 1 | Org 2 |
| 1 | Org 1 |
personOrganization are persons and the organization they work for, columns are
| OrgFK | PersonFK | isPrimary |
|---|---|---|
| 3 | 123 | 1 |
I’m trying to create a query that pulls the orgFK where isPrimary is 1 from the personOrganization table and runs it against the organization table and lists the OrgName of the parent and parents parent.
So something like this
| personFK | PrimaryOrgFK | PrimaryOrgName | ParentOrgName | ParentParenOrgName |
|---|---|---|---|---|
| 123 | 3 | Org 3 | Org 2 | Org 1 |
I want to point out that the PK and FK are not sequential as in my example above so I cant use something like OrgFK -1 = Parent, the sequential numbers above are just to illustrate the example of child and parent organizations.
Or maybe a better approach would be to make a Derived Table of only the organization table that lists out all the Organizations AND the parents' names?
Thanks in advance!