CODEWITHSUNDEEP
mysql
OpIvy
OpIvy

Reputation: 37

MySQL Select All Rows with Distinct Count Greater Than

I have a table relationship that looks like this:

relationshipId   userId   teamId
--------------   ------   ------
1                1234     999
2                5678     999
3                1234     888
4                1234     999

What I want to do is create a query that shows me every row where the userId, teamId combination is duplicated in the table.

I know I can find the duplicated rows and the number of times they are duplicated by using a query like select userId, teamId, count(*) from relationship group by userId, teamId having count(*) > 1

The above query would return: 

userId   teamId  count
------   ------  -----
1234     999     2

What I want is a query that will show me the two rows that yielded the count of 2 so that I can access the unique relationshipIds.

So, I'm looking for a result of:

relationshipId   userId   teamId
--------------   ------   ------
1                1234     999
4                1234     999

Upvotes: 0

Views: 2511

Answers (2)

BK435
BK435

Reputation: 3176

While A.D.'s answer uses a subquery, you could also try a view:

Create view relationship_view As Select relationshipID, userID, teamID, count() from relationship having count() > 1;

Then just query off that view: select * from relationship_view group by userID, teamID;

Upvotes: 0

A.D.
A.D.

Reputation: 1180

Your query is the step to identify which relation is duplicated.

To get the data you want, you need to use this result and link it to the same table to filter on the result.

Here is the query:

select a.*
from relationship as a
, (
    select userId
    , teamId
    from relationship
    group by userId
    , teamId
    having count(*) > 1
) as b
where a.userId = b.userId
and a.teamId = b.teamId

Upvotes: 4

Related Questions