I haven't written any serious SQL for several years and was hoping someone could help me refine a fairly simple sql statement.
I have attached the ERD of the Physical Data model of what I am calling the Party Object. It is basically a subclass for representing either a Person or Organization.
The Party Type is stored in a generic reference table and is used to indicate if the party is either a person or an organization. If the type is a person, then the name will be stored in the person_master table. If the type is an organization then the name will be stored in the organization master table.
What I would like to achieve is a single query which will return all of the parties regardless if they are either a person or an organization. Here is the query I have so far:
from party_master pr,
where gt.generic_table_name = "party type"
and gt.generic_table_seq_id = gd.generic_table_seq_id
and pr.party_type_seq_id = gd.generic_data_seq_id
and pr.party_seq_id = pm.party_seq_id
and pr.party_seq_id = om.party_seq_id
My desired result would be:
party id | party type | first | last | middle | organization name
1 person | Joe | Smith | James | Null
2 person | Dave | Jones | Michael | Null
3 organization | Null | Null | Null | Acme Corp
4 person | Jeff | D | Martin | Null
I am assuming this will need to be a full outer join? Any help would be much appreciated. Thanks in Advance!