Super/Sub Type Join?

freekMEDIA · #1 (**permalink**) 02-25-05, 17:29

This one has me stumped, I'm not exactly sure where to start.

Summary of Tables:
Identifiers - this table creates supertype ID numbers, so that subtypes (Person and Organization) will be unique
- IdentifierID
- IdentifierType
- ...

Person
- PersonID
- PersonName
- ...

Organization
- OrganizationID
- OrganizationName
- ...

FundingTransaction
- FundingSourceID
- FundingRecipientID
- FundingAmount
- ...

Now in the FundingTransaction table, FundingSourceID and FundingRecipientID can be either a Person, or an Organization (based on the supertype IdentifierID).

How can I do a selection on this to grab the source, the recipient and the amount? I can't really predict what will happen when I do the joins since they'll be coming from the same tables and my brain hurts from trying to figure out how it will work.

Thanks for your help! =)

r937 · #2 (**permalink**) 02-25-05, 19:59

Code:

select coalesce(SP.PersonName
               ,SO.OrganizationName) as Source
     , coalesce(RP.PersonName
               ,RO.OrganizationName) as Recipient
     , FundingAmount         
  from FundingTransaction
left outer
  join Person as SP
    on FundingSourceID
     = SP.PersonID
left outer
  join Organization as SO
    on FundingSourceID
     = SO.OrganizationID      
left outer
  join Person as RP
    on FundingRecipientID
     = RP.PersonID
left outer
  join Organization as RO
    on FundingRecipientID
     = RO.OrganizationID

freekMEDIA · #3 (**permalink**) 02-28-05, 13:23

Thanks r937, worked great. I'm using Access (should have mentioned that), so I had to modify it slightly due to its lack of the coalesce function; I just used switch statements based on a recommendation from another site - they worked great!

I have never worked with a "properly created" super/sub type database - I have used ugly calculated fields before, but the process makes a lot of sense now. Thanks for taking the time to answer my post.

Internet Services

Web Development

Digital Marketing

Consumer Tech