MySQL query with duplicates and excluding other duplicates

Question

the name may be confusing, but I will try to explain what I'm trying to do. I have a MySQL table where I need fields:

• poster_ip
• poster_id

Now I am writing a query that returns me all the results in this table for which the number of duplicates of "poster_ip" is equal to or greater than 2 (this shows me if there are more results using the same "poster_ip"). This is easy:

SELECT * 
FROM NAME_OF_TABLE
INNER JOIN (
SELECT poster_ip
FROM NAME_OF_TABLE
GROUP BY poster_ip
HAVING COUNT( poster_ip ) >1
)dup ON NAME_OF_TABLE.poster_ip = dup.poster_ip

I don’t care if it is not optimized or it gives a huge load to the database server, I do not have this problem. It is working fine. Also, I need "SELECT *" because I need to see the whole row.

( ): , "poster_ip" , "poster_ip" "poster_id". , , "poster_id" "poster_ip" . , , . , ? .

Answer 1

SELECT DISTINCT t1.*
FROM NAME_OF_TABLE t1
INNER JOIN NAME_OF_TABLE t2
  ON t1.poster_ip = t2.poster_ip
  AND t1.poster_id <> t2.poster_id