CODEWITHSUNDEEP
mysql
user5405648
user5405648

Reputation:

MySQL: Select duplicates where another column is different?

I am already selecting the duplicates using the following query:

SELECT user_id FROM group_memberships GROUP BY `user_id` HAVING COUNT(*) >= 2

But I want to take it a bit further. I want to check the group_id column on the group_memberships table, and only fetch records where user_id is a duplicate but the duplicates have two or more different group_id column values.

So essentially only select users that belong to two different groups. I have many rows which are for the same group.

Example:

user_id        group_id
1              5
1              5
2              5
2              6

Should only return user_id 2 because 1's duplicates are the same group.

Upvotes: 0

Views: 361

Answers (1)

Salil
Salil

Reputation: 47532

Try, sql_fiddle

SELECT user_id FROM group_memberships 
               GROUP BY `user_id` 
               HAVING COUNT(DISTINCT(group_id)) >= 2

Upvotes: 1

Related Questions