Geek Answers Handbook

SQL query help with unique duplicates

I can’t think it over. 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 information for the customers we sent. However, due to the addressType table

AddressType

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

some clients have multiple addresses in the address table, creating non-identical 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 want the request to return only one row / person and return the home address, if it is available otherwise, it returns the delivery address.

It seems simple enough, and maybe it's just my cold, but it makes me scratch my head a little.

+5
source share
3 answers
SELECT 
    p.person_id,
    p.first_nm,
    p.last_nm, 
    pu.purchase_dt,
    pr.sku, 
    pr.description,
    COALESCE(ha.address_type_id, sa.address_type_id) AS address_type_id
    CASE WHEN ha.address_type_id IS NOT NULL THEN ha.city_cd ELSE sa.city_cd END AS city_cd, 
    CASE WHEN ha.address_type_id IS NOT NULL THEN ha.state_cd ELSE sa.state_cd END AS state_cd, 
    CASE WHEN ha.address_type_id IS NOT NULL THEN ha.postal_cd ELSE sa.postal_cd END AS postal_cd
FROM 
    person p 
    LEFT JOIN address ha ON p.person_id = ha.person_id AND ha.address_type_id = 1
    LEFT JOIN address sa ON p.person_id = sa.person_id AND sa.address_type_id = 2
    INNER JOIN purchase pu ON pu.person_id = p.person_id
    INNER JOIN product pr ON pr.product_id = pu.product_id
+5
source

, min (addressID) :

        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
+6

If SQL Server or another version with regular table expressions (CTE), you can do the following. CTE adds a line number column, which is grouped by a person and ordered by address_type_id. The main request has been modified to return the number 1 for each person from the CTE.

WITH cte AS
    (
    SELECT
         a.person_id,
         a.address_type_id,
         a.city_cd, 
         a.state_cd, 
         a.postal_cd,
         ROW_NUMBER() over (PARTITION BY person_id ORDER BY address_type_id) AS sequence
    FROM address a
    INNER JOIN AddressType at ON a.address_type_id = at.address_type_id
    )

    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 cte 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
    WHERE
        a.sequence = 1

By the way, if you have records of people who don't have addresses, you can change INNER JOIN to OUTER JOIN in the address table (cte in my answer). It may also be suitable for joining the purchase and product if necessary for your requirements.

+1
source

More articles:


All Articles