locked
MySQL 5.5.62 group_concat with where clause RRS feed

  • Question

  • User1151703306 posted

    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');
    Friday, January 29, 2021 4:06 PM

Answers

  • User269602965 posted

    Exclude your email list in the INNER JOIN table using a SELECT and WHERE, not in the WHERE clause below the JOIN

    INNER JOIN (SELECT * FROM tb12 WHERE qEmail NOT IN ({your exclusion list})) q ON t.tCounty = q.qCountry

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 31, 2021 4:05 AM