All we need is an easy explanation of the problem, so here it is.
In a [member] table, some rows have the same value for the
login_id | email ---------|--------------------- john | [email protected] peter | [email protected] johnny | [email protected] ...
Some people used a different login_id but the same email address, no unique constraint was set on this column. Now I need to find these rows and see if they should be removed.
What SQL statement should I use to find these rows? (MySQL 5)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This query will give you a list of email addresses and how many times they’re used, with the most used addresses first.
SELECT email, count(*) AS c FROM TABLE GROUP BY email HAVING c > 1 ORDER BY c DESC
If you want the full rows:
select * from table where email in ( select email from table group by email having count(*) > 1 )
select email from mytable group by email having count(*) >1
Here is query to find
SELECT email FROM table GROUP BY email HAVING count(*) > 1
You’ll need second (of nested) query to get list of
First part of accepted answer does not work for MSSQL.
This worked for me:
select email, COUNT(*) as C from table group by email having COUNT(*) >1 order by C desc
use this if your email column contains empty values
select * from table where email in ( select email from table group by email having count(*) > 1 and email != '' )
Thanks guys 🙂 I used the below because I only cared about those two columns and not so much about the rest. Worked great
select email, login_id from table group by email, login_id having COUNT(email) > 1
I know this is a very old question but this is more for someone else who might have the same problem and I think this is more accurate to what was wanted.
SELECT * FROM member WHERE email = (Select email From member Where login_id = [email protected])
This will return all records that have [email protected] as a login_id value.
Get the entire record as you want using the condition with inner select query.
SELECT * FROM member WHERE email IN (SELECT email FROM member WHERE login_id = [email protected])
This works best
SELECT RollId, count(*) AS c FROM `tblstudents` GROUP BY RollId HAVING c > 1 ORDER BY c DESC
Very late to this thread, but I had a similar situation and the following worked on MySQL. The following query will also return all the rows that match the condition of duplicate emails
SELECT * FROM TABLE WHERE EMAIL IN (SELECT * FROM (SELECT EMAIL FROM TABLE GROUP BY EMAIL HAVING COUNT(EMAIL) > 1) AS X);
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂