There are around 5 million users in a table having 4 columns, for each value in userid column there needs to be only one sid value in same table. But there are some corrupt records where some userid having multiple sid. How to retrieve these users using mysql query. Please find below the table structure:
id userid sid
1 12345 a
2 67890 b
3 23456 c
4 45678 d
5 23456 e
6 56789 f
7 78901 g
8 10987 h
9 78901 i
10 45800 j
Here the corrupt records are 23456 and 78901. How to retrieve such user ids in mysql
>Solution :
To find duplicate entries you can use the GROUP BY and HAVING clauses:
SELECT userid
FROM your_table
GROUP BY userid
HAVING COUNT(DISTINCT sid) > 1;