Filter duplicate rows in mysql by using customer id

Question

I have two tables. customer table It has 3 fields.. - customer_id - first_name - last_name customer address table It has 2 fields - customer_id - address My problem is my customer table has 259 rows where as my customer address table has 400+ rows. I mean my customer address table contains duplicate rows. Now i would like select only unique rows from my customer address table... Can anyone help me? Thanks

Accepted Answer

In the event that the `customer_id` and `address` are the same for each duplicate, you could use `DISTINCT`:

```
SELECT DISTINCT(`customer_id`), `address` FROM `customer_addresses`;
```

If a customer has two different addresses in the table, you will receive two results with the above query. To get a single result per customer, you can use `GROUP BY`:

```
SELECT `customer_id`, `address` FROM `customer_addresses` GROUP BY `customer_id`;
```

This will be guaranteed to return a single result per-customer.

To build upon the use of `GROUP BY`, you can also use it to find the customers with duplicate entries:

```
SELECT `customer_id` FROM `customer_addresses` GROUP BY `customer_id` HAVING COUNT(*) > 1;
```

This will return only the customer's that have duplicate entries (i.e. - a count of >1) in the `customer_addresses` table - which may help you resolve your duplicate problem.

Filter duplicate rows in mysql by using customer id

Solution

Problem