Select and display only duplicate records in MySQL

Question

This question is pretty simple I for some reason can't get the proper result to display only the duplicate records

Table   : Paypal_ipn_orders id                              payer_email 1                               [email protected] 2                               [email protected]    3                               [email protected] 4                               [email protected] 5                               [email protected]  SELECT id, COUNT( payer_email ) `tot` FROM paypal_ipn_orders GROUP BY payer_email HAVING `tot` >1 

sample output

id       tot 1         2 4         2 

expected output

id       payer_email  1        [email protected] 3        [email protected] 4        [email protected] 5        [email protected] 

How do I make this happen?

Answer 1

SELECT id, payer_email FROM paypal_ipn_orders WHERE payer_email IN (     SELECT payer_email     FROM paypal_ipn_orders     GROUP BY payer_email     HAVING COUNT(id) > 1 ) 

sqlfiddle

Answer 2

The IN was too slow in my situation (180 secs)

So I used a JOIN instead (0.3 secs)

SELECT i.id, i.payer_email FROM paypal_ipn_orders i INNER JOIN (  SELECT payer_email     FROM paypal_ipn_orders      GROUP BY payer_email     HAVING COUNT( id ) > 1 ) j ON i.payer_email=j.payer_email