Mysql - List duplicate values grouped by category

Question

I have this table

t_contacts

| id | gid | email            | desc |
+----+-----+------------------+------+
|  1 |  1  | foo@email.com    |      |
|  2 |  1  | bar@email.com    |      |
|  3 |  1  | foo@email.com    |  c   |
|  4 |  1  | foo@email.com    |  c   |
|  5 |  2  | foo@email.com    |  a   |
|  6 |  2  | bar@email.com    |      |
|  7 |  2  | doe@email.com    |  x   |
|  8 |  2  | doe@email.com    |  x   |

I would like to list all the equal email values with the same gid (grouped gid?) in order to obtain this result

| id | gid | email            | desc |
+----+-----+------------------+------+
|  1 |  1  | foo@email.com    |      |
|  3 |  1  | foo@email.com    |  c   |
|  4 |  1  | foo@email.com    |  c   |
|  5 |  2  | foo@email.com    |  a   |
|  7 |  2  | doe@email.com    |  x   |
|  8 |  2  | doe@email.com    |  x   |

I tried to do this, but I can't find duplicate records broken down by groups.

SELECT cid, gidid, email
FROM t_contacts
WHERE email IN (
  SELECT email FROM t_contacts GROUP BY email HAVING count(email) > 1
)
ORDER BY email

How could I do it? Thank you

Answer 1

If the task is "Select all records which have the same (email, gid) pair valies", then

SELECT t1.*
FROM t_contacts t1
WHERE EXISTS ( SELECT NULL
               FROM t_contacts t2
               WHERE t1.email = t2.email
                 AND t1.gid = t2.gid
                 AND t1.id != t2.id )

---

It the task is "Select all records with email, for which there exists 2 records with the same gid", then look for @D-Shih's solution.

Answer 2

You can try to use JOIN with a subquery.

Schema (MySQL v5.7)

CREATE TABLE t_contacts(
   id int,
   gid int, 
  email varchar(500),
   `desc` varchar(5)
);


INSERT INTO t_contacts VALUES (1 ,1 ,'foo@email.com',' ');
INSERT INTO t_contacts VALUES (2 ,1 ,'bar@email.com',' ');
INSERT INTO t_contacts VALUES (3 ,1 ,'foo@email.com','c');
INSERT INTO t_contacts VALUES (4 ,1 ,'foo@email.com','c');
INSERT INTO t_contacts VALUES (5 ,2 ,'foo@email.com','a');
INSERT INTO t_contacts VALUES (6 ,2 ,'bar@email.com',' ');
INSERT INTO t_contacts VALUES (7 ,2 ,'doe@email.com','x');
INSERT INTO t_contacts VALUES (8 ,2 ,'doe@email.com','x');

---

Query #1

SELECT t1.*
FROM t_contacts t1 JOIN (
    SELECT email
    FROM t_contacts 
    GROUP BY email,gid
    HAVING count(email) > 1
) t2 on t1.email = t2.email 
ORDER BY t1.id;

| id  | gid | email         | desc |
| --- | --- | ------------- | ---- |
| 1   | 1   | foo@email.com |      |
| 3   | 1   | foo@email.com | c    |
| 4   | 1   | foo@email.com | c    |
| 5   | 2   | foo@email.com | a    |
| 7   | 2   | doe@email.com | x    |
| 8   | 2   | doe@email.com | x    |

---

View on DB Fiddle