Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Help with Joining Party Objects

    Hi,

    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:

    select pr.party_seq_id,
    gd.generic_data_val,
    pm.name_first,
    pm.name_last,
    pm.name_middle,
    om.organization_name
    from party_master pr,
    lookup_generic_table gt,
    lookup_generic_data gd,
    person_master pm,
    organization_master om
    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!

    Regards
    Jeff
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    I do not know if I understand correctly but try something like that:

    Code:
    select 
        pr.party_seq_id,
        gd.generic_data_val,
        pm.name_first,
        pm.name_last,
        pm.name_middle,
        om.organization_name
    from party_master pr
    inner join lookup_generic_data gd 
        on gd.generic_data_seq_id = pr.party_type_seq_id
    inner join lookup_generic_table gt 
        on gt.generic_table_seq_id = gd.generic_table_seq_id
    left join person_master pm 
        on pm.party_seq_id = pr.party_seq_id
    left join organization_master om 
        on om.party_seq_id = pr.party_seq_id
    where gt.generic_table_name = "party type"
    Hope this helps.

Tags for this Thread

Posting Permissions

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