Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Question Super/Sub Type Join?

    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! =)
    --

    Travis Nelson
    http://www.travis-nelson.com/

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25
    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.
    --

    Travis Nelson
    http://www.travis-nelson.com/

Posting Permissions

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