CODEWITHSUNDEEP
mysql
eevaa
eevaa

Reputation: 1457

MySQL find pairs in table

My table looks like this

+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
|  1 |  a |  x | 13 |
|  2 |  b |  z | 27 |
|  3 |  c |  y | 24 |
|  4 |  a |  y | 11 |
|  5 |  a |  x | 19 |
|  6 |  b |  y | 22 |
|  7 |  b |  z | 20 |
+----+----+----+----+

I need to find all rows with a duplicate (c1, c2) value pair.

How would I do that?

Upvotes: 1

Views: 183

Answers (3)

CMPS
CMPS

Reputation: 7769

Using WHERE:

SELECT t1.c1, t1.c2, t2.c1, t2.c2
FROM table t1, table t2
WHERE t1.c1 = t2.c1 
AND t1.c2 = t2.c2
AND t1.id != t2.id

Using INNER JOIN

SELECT t1.c1, t1.c2, t2.c1, t2.c2
FROM table t1
INNER JOIN table t2
ON t1.c1 = t2.c1 
AND t1.c2 = t2.c2
AND t1.id != t2.id

Explanation:

t1 and t2 refer to the same table.
Search in the table t1 records that have the same c1 and c2 but different ID in t2.

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

you can use join also 

select t.* from t join (
select * from t group by c1,c2 having count(*) >1
) t1 using(c1,c2)

Demo

Upvotes: 0

juergen d
juergen d

Reputation: 204766

select * from your_table
where id not in
(
  select min(id)
  from your_table
  group by c1, c2
  having count(*) = 1
)

Upvotes: 1

Related Questions