In a [member] table, some rows have the same value for the email
column.
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)
select email from mytable group by email having count(*) >1
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 != ''
)
Here is query to find email
's which are used for more then one login_id
:
SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1
You'll need second (of nested) query to get list of login_id
by email
.
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])
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.
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
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
Source: Stackoverflow.com