SQL query help with non-unique duplicates

Question

I can't think through this one. I have this query:

SELECT 
    p.person_id,
    p.first_nm,
    p.last_nm, 
    pu.purchase_dt,
    pr.sku, 
    pr.description,
    a.address_type_id,
    a.city_cd, 
    a.state_cd, 
    a.postal_cd
FROM 
    person p 
    INNER JOIN address a ON p.person_id = a.person_id
    INNER JOIN purchase pu ON pu.person_id = p.person_id
    INNER JOIN product pr ON pr.product_id = pu.product_id

Simple enough - I just need to get the information for customers that we've shipped returns to. However, because of the addressType table

AddressType

address_type_id    address_type_desc
------------------------------------
1            Home
2            Shipping

some customers have multiple addresses in the address table, creating non-unique duplicate entries like this.

1,Smith, John, 12/01/2009, A12345, Purple Widget, 1, Anywhere, CA, 12345
1,Smith, John, 12/01/2009, A12345, Purple Widget, 2, Somewhere, ID, 54321

I'd like to get the query to return just one row/person and return the home address if available otherwise, return the shipping address.

This seems simple enough, and maybe it's just my cold, but this is causing me to scratch my head somewhat.

Answer 1

you want to change your join so it returns the min(addressID) instead of all of them:

        INNER JOIN address a ON p.person_id = a.person_id
        inner join (select person_id, min(address_type_id) as min_addr 
from address group by person_id) a_min 
on a.person_id = a_min.person_id and a.address_type_id = a_min.min_addr