MySQL 5.5.62 group_concat with where clause

[Guido1971]

Hi all,

I got this problem with Group_Concat and a where filter using query INNER JOIN and MySQL 5.5.62 version.

This is the query (I’m sorry but if enter the query text the question is not validated…)

Why column group_emails contains edwin@xxxxx.com; laura@xxxxx.com values if the condition WHERE I have set NOT IN clause?

AND q.qEmail NOT IN ( 'leon@xxxxx.com', 'edwin@xxxxx.com' 'laura@xxxxx.com' );

I need this output

+-----+----------+---------------------+-----------------------------------------+
| tID | tCountry | tStartDate          | group_emails                            |
+-----+----------+---------------------+-----------------------------------------+
|   1 | ABW      | 2021-01-01 15:47:31 | admin@xxxxx.com                         |
+-----+----------+---------------------+-----------------------------------------+

Help me to do it.

My MySQL table below

-- ----------------------------
-- Table structure for tbl1
-- ----------------------------
DROP TABLE IF EXISTS `tbl1`;
CREATE TABLE `tbl1`  (
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  `tCountry` varchar(255) NULL DEFAULT NULL,
  `tStartDate` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of tbl1
-- ----------------------------
INSERT INTO `tbl1` VALUES (1, 'ABW', '2021-01-01 15:47:31');
INSERT INTO `tbl1` VALUES (2, 'AFG', '2021-01-02 15:47:46');
INSERT INTO `tbl1` VALUES (3, 'AGO', '2021-01-03 15:47:51');
INSERT INTO `tbl1` VALUES (4, 'AIA', '2021-01-06 15:47:56');
INSERT INTO `tbl1` VALUES (5, 'ALB', '2021-01-08 15:48:00');
INSERT INTO `tbl1` VALUES (6, 'AND', '2021-01-15 15:48:03');
INSERT INTO `tbl1` VALUES (7, 'ANT', '2021-01-16 15:48:08');
INSERT INTO `tbl1` VALUES (8, 'ARE', '2021-01-18 15:48:12');
INSERT INTO `tbl1` VALUES (9, 'ARG', '2021-01-20 15:48:15');
INSERT INTO `tbl1` VALUES (10, 'ARM', '2021-01-29 15:48:18');

-- ----------------------------
-- Table structure for tbl2
-- ----------------------------
DROP TABLE IF EXISTS `tbl2`;
CREATE TABLE `tbl2`  (
  `qID` int(11) NOT NULL AUTO_INCREMENT COMMENT ' ',
  `qCountry` varchar(255) NULL DEFAULT NULL,
  `qEmail` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`qID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of tbl2
-- ----------------------------
INSERT INTO `tbl2` VALUES (1, 'ABW', 'leon@xxxxx.com');
INSERT INTO `tbl2` VALUES (2, 'AGO', 'leon@xxxxx.com');
INSERT INTO `tbl2` VALUES (3, 'ABW', 'edwin@xxxxx.com');
INSERT INTO `tbl2` VALUES (4, 'ALB', 'laura@xxxxx.com');
INSERT INTO `tbl2` VALUES (5, 'ABW', 'admin@xxxxx.com');

[r937]

your problem is, you forgot the GROUP BY clause

and since i shudder at including it in the outer query, i prefer to do it in a subquery, because that allows localizing the NOT IN condition

also, LEFT OUTER seems warranted in this case, since the NOT IN condition might completely wipe out the emails for a given country

SELECT t.tID
     , t.tCountry
     , t.tStartDate
     , s.group_emails
  FROM tbl1 AS t
LEFT OUTER
  JOIN ( SELECT tCountry
              , GROUP_CONCAT(DISTINCT qEmail
                             ORDER BY qEmail ASC
                             SEPARATOR '; ' )
                 AS group_emails 
           FROM tbl2
          WHERE qEmail NOT IN ( 'leon@xxxxx.com'
                              , 'edwin@xxxxx.com'
                              , 'laura@xxxxx.com' ) 
         GROUP
             BY tCountry ) AS s  
    ON s.tCountry = t.tCountry      
 WHERE t.tStartDate BETWEEN CURRENT_DATE - INTERVAL 3 MONTH
                        AND CURRENT_DATE     

[Guido1971]

thanks for reply, but i have error (I’m sorry but if enter the query text the question is not validated…)

[r937]

my bad

obviously, it should be qCountry

but that’s because i had to completely retype everything instead of being able to copy/paste, because you posted only an image of your query

please see Images of code

[Guido1971]

thank you for help… now the return is

+-----+----------+---------------------+-----------------+
| tID | tCountry | tStartDate          | group_emails    |
+-----+----------+---------------------+-----------------+
|   1 | ABW      | 2021-01-01 15:47:31 | admin@xxxxx.com |
+-----+----------+---------------------+-----------------+
1 row in set (0.03 sec)

But I can’t publish your query edited, I don’t know what happen on this forum