MYSQL: see unique row results when some fields are not filled

I got the following question about mysql

First I'll show my DB, then what I want and then what I did and did not work

I got the following table

DB:customeradress

+-------------------+-------------------+-------------------+---------------+---------------+
|customeradress_id | customername_id | customeradres  | customerZip  | customerplace |
+-------------------+-------------------+-------------------+---------------+---------------+
+-------------------+-------------------+-------------------+---------------+---------------+
|01     | 01    | streetA 1   | 1234AB  | PlaceA  |
+-------------------+-------------------+-------------------+---------------+---------------+
|02     | 04    | streetZ 99  | 9901ZZ  | PlaceZ  |
+-------------------+-------------------+-------------------+---------------+---------------+
|03     | 99    | streetXY 11  |     | PlaceB  |
+-------------------+-------------------+-------------------+---------------+---------------+
|04     | 01    | streetA 1   |     | PlaceA  |
+-------------------+-------------------+-------------------+---------------+---------------+
|05     | 01    | streetA 1   | 1234AB  | PlaceA  |
+-------------------+-------------------+-------------------+---------------+---------------+

What I want:

I want to do a query on the DB thats gives me the unique records and, when there are multiple records with 1 that has a customerZip an one that doesn't, then return the one with the customerZip filled. The result should look like this:

+-----------------------+
|customeradress_id  |
+-----------------------+
+-----------------------+
|01      |
+-----------------------+
|02      |
+-----------------------+
|03      |
+-----------------------+

What I did:

When I use this 开发者_如何转开发query:

MYSQL > SELECT DISTINCT customeradress_id FROM customeradress

and

MYSQL > SELECT customeradress_id FROM customeradress GROUP BY customeradress_id

I get the following result

+-----------------------+
|customeradress_id  |
+-----------------------+
+-----------------------+
|01      |
+-----------------------+
|02      |
+-----------------------+
|03      |
+-----------------------+
|04      |
+-----------------------+

Can somebody help me?

Rationale goes like this

• The inner select get's all unique customernames. If a customer has a zip, the MIN (or MAX) retrieves one zipcode.
• Joining the inner select again with the original table removes those rows where a customer has a NULL zipcode (if another zipcode is available)

SQL Statement

SELECT  DISTINCT customeradress_id
FROM    customeradress ca
        INNER JOIN (
          SELECT  customername_id
                  , customerZip = MIN(customerZip)
          FROM    customeradress
          GROUP BY
                  customer_name_id
        ) cag ON cag.customername_id = ca.customername_id
                 AND ISNULL(cag.customerZip, '') = ISNULL(ca.customerZip, '')

First, you want the duplicate customer addresses which is the set of customer_id & customeradress pairs in more than one row. Then, you want to find all rows matching those customer_id and customeradres pairs and filter out the nulls.

Try this:

select ca.customeradress_id
from
    customeradress ca inner join
    (select customer_id, customeradres
     from customeradress
     group by customer_id, customeradres
     having count(*) > 1) t1 on t1.customer_id = ca.customer_id and t1.customeradres = ca.customeradres
where
    ca.customerZip is not null

The subselect groups the rows on customer_id and customeradres and the having clause filters to only those with more than one occurence, i.e. the duplicates. This set of customer_id and customeradres is inner joined to the same table so that only matching rows are returned. The where clause then filters to only the ones with a ZIP.