CODEWITHSUNDEEP
mysqlsql
vietean
vietean

Reputation: 3033

Select the fields are duplicated in mysql

Assuming that I have the below customer_offer table.

My question is:

How to select all the rows where the key(s) are duplicated in that table?

+---------+-------------+------------+----------+--------+---------------------+
| link_id | customer_id | partner_id | offer_id | key    | date_updated        |
+---------+-------------+------------+----------+--------+---------------------+
|       1 |          99 |         11 |       14 | mmmmmq | 2011-09-21 12:40:46 |
|       2 |         100 |         11 |       14 | qmmmmq | 2011-09-21 12:40:46 |
|       3 |         101 |         11 |       14 | 8mmmmq | 2011-09-21 12:40:46 |
|       4 |          99 |         11 |       14 | Dmmmmq | 2011-09-21 12:59:28 |
|       5 |         100 |         11 |       14 | Nmmmmq | 2011-09-21 12:59:28 |
+---------+-------------+------------+----------+--------+---------------------+

UPDATE:

Thanks so much for all your answer. There are many answers are good. Now I got the solution to do.

Upvotes: 1

Views: 93

Answers (6)

Ghazanfar Mir
Ghazanfar Mir

Reputation: 3543

SELECT link_id, key, count(key) as Occurrences 
FROM table 
GROUP BY key
HAVING COUNT(key)>1;

Upvotes: 0

xdazz
xdazz

Reputation: 160833

select * 
from customer_offer 
where key in 
(select key from customer_offer group by key having count(*) > 1)

Update: As mentioned from @Scorpi0, if with a big table, it is better to use join. And from mysql6.0 the new optimizer will convert this kind of subqueries into joins.

Upvotes: 3

Cyril Gandon
Cyril Gandon

Reputation: 17048

SELECT DISTINCT c1.* 
FROM customer_offer c1 
    INNER JOIN customer_offer c2
        ON c1.key = c2.key
        AND c1.link_id != c2.link_id

Assuming link_id is a primary key.

Upvotes: 1

Matt Seymour
Matt Seymour

Reputation: 9395

There are many threads on the mysql website which explains how to do this. This link will explain how to do this using mysql: http://forums.mysql.com/read.php?10,180556,180567#msg-180567

As a brief example the code below is from the link with a slight modification which better suits your example. 

SELECT *
FROM tbl
GROUP BY key
HAVING COUNT(key)>1;

You can also use a joing which is my prefered method, as this removes the slower count method:

SELECT * 
FROM this_table t 
inner join this_table t1 on t.key = t1.key

Upvotes: 0

Ben Lee
Ben Lee

Reputation: 53309

Use a sub-query to do the count check, and the main query to select the rows. The count check query is simply:

SELECT `link_id` FROM `customer_offer` GROUP BY `key` HAVING COUNT(`key`) > 1

Then the outer query will use this by joining into it:

SELECT customer_offer.* FROM customer_offer
INNER JOIN (SELECT `link_id` FROM `customer_offer` GROUP BY `key` HAVING COUNT(`key`) > 1) AS count_check
ON customer_offer.link_id = count_check.link_id

Upvotes: 0

Rami Alshareef
Rami Alshareef

Reputation: 7140

Self join

SELECT * FROM customer_offer c1 inner join customer_offer c2
on c1.key = c2.key

or group by the field then take when count > 1

SELECT COUNT(key),link_id FROM customer_offer c1
group by key, link_id
having COUNT(Key) > 1

Upvotes: 1

Related Questions