I have a table that has 4 e-mail fields. A single contact can have anywhere from 1-4 e-mail addresses on file.
I'm having trouble writing a query that displays all duplicates across columns. For my example, I'll only discuss 2 email fields.
- John Smith, Personal Email firstname.lastname@example.org Work email@example.com
- Jane Smith, Personal Email firstname.lastname@example.org Work NULL
- Jane Smith, Personal Email NULL Work email@example.com
In the above example, I can't find a way to say "I have 2 Jane Smiths that are duplicates" because they have the same email, but in different columns while also excluding John Smith, because while his email appears twice, I don't want to self-reference, as this is not a duplicate of itself.
Iterations of my attempt to find a cross-column duplication check have been unsuccessful but include the following:
Select First, Last, Personal From Contacts C where C.Personal = C.Work and c.ID <> c.ID -- my attempt to prevent self reference
This doesn't work, I receive 0 results, but should have thousands.
c.ID <> c.ID bit, I receive too many results, as I retrieve results for non-duplicate records that simply have the same email address in both fields.
I've also added "Group By" clauses based on First & Last name and filter by greater than 1, but when checking our database, I'm still finding that I have contacts with the same name and no e-mail addresses in common with each other but have the same scenario as John Smith above.
Is there any way to achieve this? I haven't found any queries to get me started.