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.
If you want the query to return only unique rows, use the keyword DISTINCT after SELECT . DISTINCT can be used to fetch unique rows from one or more columns.
One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With