SQL Query with COUNT, Having Count >1, display full details of duplicates

Question

I have a table like :

name  employment_Status  email
----     ----             -----
David     E              David@email.com
John      U              John@email.com
Michael   E              Michael@email.com
Steve     E              Michael@email.com
James     U              David@email.com
Mary      U              Mary@email.com
Beth      E              Beth@email.com

I started by selecting email and count(email):

SELECT email, COUNT(email) AS emailCount
FROM Table
GROUP BY email
HAVING ( COUNT(email) > 1 );

The problem occurred when I tried to include name as well:

SELECT name, email, COUNT(email) AS emailCount
FROM Table
GROUP BY name, email
HAVING ( COUNT(email) > 1 );

I would like to find all people with a duplicate email addresses, (only where both people are employed (E)). However it is returning zero results.

I'd like to be able to display all information for people with duplicate emails, and having employment_Status E. If two people have the same email, but one or both is Unemployed (U), then just ignore.

Could anyone advise?

Answer 1

I think you want exists:

select t.*
from t
where t.employeed = 'E' and
     exists (select 1
             from t t2
             where t2.email = t.email and t2.employeed = 'E' and
                   t2.name <> t.name
            );

Note that this assumes that name (or at least name/email) is unique.

In MySQL 8+, you can use window functions:

select t.*
from (select t.*, count(*) over (partition by t.email) as cnt
      from t
      where t.employeed = 'E'
     ) t
where cnt >= 2;