Filtering out unique rows in MySQL
- by jpatokal
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
