CODEWITHSUNDEEP
mysql
Blue Moon
Blue Moon

Reputation: 4661

MySQL group rows that share at least one value in common on multiple columns

Consider the following table: 

CREATE TABLE `customer_identifiers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueness` (`order_id`,`email`,`phone`),
  KEY `email` (`email`),
  KEY `order` (`order_id`),
  KEY `phone` (`phone`),
  KEY `CA` (`created_at`),
  KEY `UA` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

insert into dev.customer_identifiers(order_id,email,phone) values (1,'[email protected]','07444226373'),
 (2,'[email protected]','0744422633'), 
 (3,'[email protected]','07444226373'),
 (4,'[email protected]','07453456373'), 
 (5,'[email protected]','07955226373');

enter image description here

How could I group of all order ids that share either the same email or the same phone number?

desired output:

+----------+------------------------+--------------------------------+
| order_id | phone                  | mail                           |
+----------+------------------------+--------------------------------+
| 1,2,3    | 07444226373,0744422633 | [email protected],[email protected] |
+----------+------------------------+--------------------------------+
| 4        | 07453456373            | [email protected]                |
+----------+------------------------+--------------------------------+
| 5        | 07955226373            | [email protected]                |
+----------+------------------------+--------------------------------+

Upvotes: 3

Views: 127

Answers (2)

Alexey
Alexey

Reputation: 3484

SELECT * FROM 
(
    SELECT ci2.`order_id`,GROUP_CONCAT(ci2.`order_id`) AS `concats`,GROUP_CONCAT(DISTINCT ci2.`phone`) as phones,GROUP_CONCAT(DISTINCT ci2.`email`) as mails 
    FROM `customer_identifiers` ci1
    INNER JOIN `customer_identifiers` ci2 ON ci1.`email` = ci2.`email` OR ci1.`phone` = ci2.`phone`
    GROUP BY ci1.`order_id`
) AS tbl1 
GROUP BY tbl1.`order_id`;

Upvotes: 2

Kim Lage
Kim Lage

Reputation: 117

What you should do is to count the number of duplicated rows:

SELECT 
email,
phone,
COUNT(email) AS count_email,
COUNT(phone) AS count_phone
FROM customer_identifiers
GROUP BY email,phone
HAVING
COUNT(email)>1  OR COUNT(phone) > 1

You can personalize to return the columns you need to identify the ids that have the duplicity.

I hope it helps...

Upvotes: -1

Related Questions