Finding mySQL rows that have two matching fields

Question

I am trying to write a query that will return rows that have two matching fields.

|-------------|-------------|----------|
|      id     |   field 1   |  field 2 |
|-------------|-------------|----------|
|      1.     |      a      |     z    | #
|-------------|-------------|----------|
|      2.     |      b      |     x    |
|-------------|-------------|----------|
|      3.     |      c      |     y    | ##
|-------------|-------------|----------|
|      4.     |      a      |     z    | #
|-------------|-------------|----------|
|      5.     |      b      |     z    |
|-------------|-------------|----------|
|      6.     |      c      |     y    | ##
|-------------|-------------|----------|

The rows marked above would be returned from such a query. They are essentially duplicates. I tried inner joining the table on itself where field 1 = field 1 and field 2 = field 2, to no avail. Can this be done without writing a PHP script with multiple queries?

Does this help? https://stackoverflow.com/questions/8149210/how-do-i-find-duplicates-across-multiple-columns — modusTollens, Nov 29 '18 at 22:37
If you have MySQL V8.0 or later you can use `count(*) over(partition by ...)` [shown here](https://stackoverflow.com/a/53548941/2067753) — Paul Maxwell, Nov 29 '18 at 23:10

Frank Conry · Answer 1 · 2018-11-29T22:41:49.473

0

Sorry, I misunderstood. You are looking for something like this:

SELECT GROUP_CONCAT(t1.id), t1.f1, t1.f2 
FROM t t1 
CROSS JOIN t t2 
WHERE t1.f1=t2.f1 AND t2.f2=t1.f2 AND t1.id != t2.id
GROUP BY t1.f1, t1.f2

edited Nov 29 '18 at 22:41

answered Nov 29 '18 at 22:33

Frank Conry

2,694
3
29
35

This does not account for field 1. They could have B in field 1 and Y in field 2. – modusTollens Nov 29 '18 at 22:36
I went ahead an updated it. I assume you want the feidl1 and feild2 as the result, not the ids? – Frank Conry Nov 29 '18 at 22:42
added group by now in case you wanted ids – Frank Conry Nov 29 '18 at 22:45

score 0 · Answer 2 · answered Nov 29 '18 at 22:40

0

SELECT t.*
FROM mytable t
INNER JOIN (
   SELECT field_1, field_2
   FROM mytable
   GROUP BY field_1, field_2
   HAVING COUNT(id)>1
) f
ON t.field_1 = f.field_1
   AND t.field_2 = f.field_2

answered Nov 29 '18 at 22:40

Alex

16,739
1
28
51

Finding mySQL rows that have two matching fields

2 Answers2