CODEWITHSUNDEEP
mysql
luqita
luqita

Reputation: 4077

MySQL: Selecting duplicates among many fields

I am using the following query to select emails that are duplicates:

SELECT email
                FROM contacts
                WHERE active = 1
                GROUP BY email
                HAVING ( COUNT(email) > 1)

In the case above, if two users have the same email, that query will show them. However, there is another email field named email2, what can I do so that I compare the count among many email fields? (So, for example, if a user has email equal to email2 of another user, they will be considered duplicates)

Thank you!

Upvotes: 0

Views: 84

Answers (2)

frail
frail

Reputation: 4118

SELECT tmp.email FROM (
    SELECT email as email FROM contacts WHERE active = 1
    UNION ALL
    SELECT email2 as email FROM contacts WHERE email != email2 AND active = 1
) as tmp
GROUP by tmp.email
HAVING COUNT(tmp.email) > 1

P.S : my syntax could be wrong, but the idea should be something like this.

Upvotes: 2

Marc B
Marc B

Reputation: 360572

SELECT email
FROM contacts
WHERE (email = email2) AND (active = 1)

if I'm interpreting you correctly. Or do you mean that some user records may have "[email protected]" in the email field in one record, and the same address in the email2 field in another record? that'd be

SELECT primary.email, primary.id, secondary.id
FROM contacts AS primary
INNER JOIN contacts AS secondary ON (primary.email = secondary.email2)

Upvotes: -1

Related Questions