CODEWITHSUNDEEP
mysql
PrivateUser
PrivateUser

Reputation: 4534

Filter duplicate rows in mysql by using customer id

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

Upvotes: 5

Views: 14659

Answers (2)

newfurniturey
newfurniturey

Reputation: 38456

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.

Upvotes: 12

Daniel F. Thornton
Daniel F. Thornton

Reputation: 3685

Add the DISTINCT keyword to your SQL statement.

Reference: http://www.techonthenet.com/sql/distinct.php

Upvotes: -1

Related Questions