SQL query help!!

[MrT]

Hi,

I have two views:

   Address_detail_view
    KEY_ID
    KEY_TYPE
    ADDRESS_TYPE
    ADDRESS_LINE1,
    ADDRESS_LINE2,
    CITY,
    STATE,
    ZIP


  renewal_view 
   TRANSACTION_KEY,
   CUSTOMER_KEY

In the address_detail_view, the key_type would specify whether CUSTOMER, TRANSACTION, DEPARTMENT. The address_type would specify if it is HOME,WORK,SHIPPING,BUSINESS, BILLING

Now comes the fun part. I want to query the renewal_view and display the addresses from the address_detail_view using the transaction and customer keys mapped to key_id. The problem I'm having is to see how to display all the items from the renewal_view and get the corresponding addresses where ever available.

Could some one tell me how? I'm lost in here

Thanks,
Mr. T

[dknight]

I will guess, but it might help if you put some sample data on your post.

You could refer to Address Detail View as ADV1 and ADV2 and Renewal View as RV

Join such that:

where RV.Transaction_Key = ADV1.Key_ID
and RV.Customer_Key=ADV2.Key_ID

[MrT]

Dknight,

I can get what you say, but it doesn't seem to match to the problem what I have. The issue for me was to retrive the columns that wouldn't have an entry in the address_detail view. It is possible at times there would not be an customer_id to key_id mapping or transaction_id_to key_id mapping or neither of the two.

my approach was

   WHERE
       (AV.KEY_ID (+) = RV.CUSTOMER_ID
         AND  AV.KEY_TYPE(+) = 'CUSTOMER'
         AND (AV.ADDR_TYPE(+) = 'WORK'
                  OR AV.ADDR_TYPE(+) = 'HOME')
        )
        OR
          (AV.KEY_ID (+) = RV.TRANSACTION_ID
         AND  AV.KEY_TYPE(+) = 'TRANSACTION'
         AND (AV.ADDR_TYPE(+) = 'BUSINESS'
                  OR AV.ADDR_TYPE(+) = 'BILLING')
        )

Will it work? Remember, I want all the entry from the renewal_view

Thanks,

Mr.T