Get records that have equal values

Question

I want to get the contact table records that have the same email. Only the email is a field of the email table because this way the contact can have several emails (One To Many).

// Tabela contato

id name
1  Alladin
2  Dumbo
3  Pluto

// Tabela email
id email               contact_id
1  [email protected]    2
2  [email protected]  1
3  [email protected]    3

I need to get the two records that have the same email and that are different contacts, for example:

id name  email
2  Dumbo [email protected]
3  Pluto [email protected]

I tried the following query but unsuccessfully:

SELECT c.id, c.name, e.email FROM contato c
INNER JOIN email e ON e.contact_id = c.id
WHERE e.email IN (SELECT e2.email FROM email e2 GROUP BY e2.email HAVING COUNT(*) > 1);

Answer 1

To do this, just do the JOIN between your two tables, grouping them by email address and filtering the result to keep the groups that have 2 or more users for that email.

You came close to that in your SQL:

SELECT e.email, GROUP_CONCAT(c.name SEPARATOR ', ') as names
FROM contato c
JOIN email e ON e.contact_id = c.id
GROUP BY e.email
HAVING COUNT(1) > 1

Thus, the output will be a list of emails that have duplicated contacts accompanied by a column with the names of the contacts.