MySQL find duplicates based on another column value

Question

I have the following table

+----+------+-------+
| id | user | value |
+----+------+-------+
|  1 |  10  |   A   |
|  2 |  12  |   B   |
|  3 |  24  |   A   |
|  4 |  33  |   C   |
+----+------+-------+

I want to retreive all the duplicates users that have the same key

+----+------+-------+
| id | user | value |
+----+------+-------+
|  1 |  10  |   A   |
|  3 |  24  |   A   |
+----+------+-------+

I've tried that with no luck

SELECT DISTINCT A.user, A.value
FROM table as A
INNER JOIN ( SELECT value FROM table GROUP BY value HAVING COUNT(value) > 1 ) AS B
ON A.value = B.value

Your code works fine: https://www.db-fiddle.com/f/4psBYGjQHKKcBhQM6DsWXQ/0 — forpas, Feb 21 '20 at 08:18
While 'minimal', a four row data set is unlikely to be 'representative' — Strawberry, Feb 21 '20 at 09:29

score 1 · Accepted Answer · answered Feb 21 '20 at 08:16

1

You may try below query -

SELECT id, user, value
FROM YUOR_TABLE T1
WHERE EXISTS (SELECT 1
              FROM YOUR_TABLE T2
              WHERE T1.value = T2.value
              AND T1.user <> T2.user)

answered Feb 21 '20 at 08:16

Ankit Bajpai

13,128
4
25
40

MySQL find duplicates based on another column value

1 Answers1

Linked