Filtering out unique rows in MySQL

Posted by jpatokal on Stack Overflow See other posts from Stack Overflow or by jpatokal
Published on 2011-02-09T07:06:41Z Indexed on 2011/02/09 7:25 UTC
Read the original article Hit count: 188

Filed under:
|
|
|

So I've got a large amount of SQL data that looks basically like this:

user | src | dst
1    | 1   | 1
1    | 1   | 1
1    | 1   | 2
1    | 1   | 2
2    | 1   | 1
2    | 1   | 3

I want to filter out pairs of (src,dst) that are unique to one user (even if that user has duplicates), leaving behind only those pairs belonging to more than one user:

user | src | dst
1    | 1   | 1
1    | 1   | 1
2    | 1   | 1

In other words, pair (1,2) is unique to user 1 and pair (1,3) to user 2, so they're dropped, leaving behind only all instances of pair (1,1).

Any ideas? The answers to the question below can find the non-unique pairs, but my SQL-fu doesn't suffice to handle the complication of requiring that they belong to multiple users as well.

[SQL question] How to select non "unique" rows

© Stack Overflow or respective owner

Related posts about mysql

Related posts about select