Filter duplicate rows in mysql by using customer id

Question

I have two tables.

customer table

It has 3 fields..

1. customer_id
2. first_name
3. last_name

customer address table

It has 2 fields

1. customer_id
2. 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

Answer 1

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.