CODEWITHSUNDEEP
mysql
veelen
veelen

Reputation: 1914

MySQL select duplicates

I have a table containing group_id, user_id, and role_id.

Because the unique key was set wrong, there are users who are in a group twice, with different roles. Like this:

group_id   |   user_id   |   role_id
    1      |      1      |      1
    1      |      1      |      2 
    2      |      1      |      1
    1      |      2      |      1
    2      |      2      |      3

How can i find the user id's of the user where this is the case? So, in this example only user id 1?

Edit:

Because neither the answer from @Manil Liyanage nor the one from @TX Turner give me the right results, maybe i have to explain it some more;

Users can be in multiple groups, but they shouldn't be in the some group more than once. Unfortunately, because of the wrong unique key, some users now are in the same group more than once, but only with another role. How can I find the users who are in a group more than once?

Upvotes: 0

Views: 83

Answers (2)

TX Turner
TX Turner

Reputation: 167

the following would show you which userids are duplicated per group, and the count.

SELECT user_id, group_id, COUNT(group_id) cnt
FROM tablename
GROUP BY user_id, group_id
HAVING COUNT(group_id) > 1

Upvotes: 2

Manil Liyanage
Manil Liyanage

Reputation: 255

Following should work just fine for you. It shows the ida of the duplicated records

EDIT: Use the keys inside the having-count statement concatenated. 

SELECT concat(concat('Group is: ',group_id) ,
concat(' user id : ' , user_id)) Places
FROM table
HAVING count(concat(group_id,concat (':',user_id))) > 1

Upvotes: 1

Related Questions