locked
When username changes on table register get only last username in mysql RRS feed

  • Question

  • User-2004582644 posted

    Hi all,

    This is my table tbl_register on MySQL database

    +---------------------+------------------+---------------------+-----+
    | sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
    +---------------------+------------------+---------------------+-----+
    | 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
    | 2017-08-05 14:01:00 | 030270           | 116019              |   2 |
    | 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
    | 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
    | 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
    | 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
    | 2016-02-24 11:37:33 | 052994 | 583725 | 7 |
    +---------------------+------------------+---------------------+-----+

    On this table tbl_register are also recorded any username changes in the column sNewRegisterAccount

    e.g.

    +---------------------+------------------+---------------------+-----+
    | sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
    +---------------------+------------------+---------------------+-----+
    | 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
    | 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
    +---------------------+------------------+---------------------+-----+

    in the column sRegisterAccount the old account value 000647

    in the column sNewRegisterAccount the new account value 542116

    I need get only last value of sNewRegisterAccount as return

    +---------------------+------------------+---------------------+-----+
    | sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
    +---------------------+------------------+---------------------+-----+
    | 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
    | 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
    | 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
    | 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
    | 2016-02-24 11:37:33 | 052994 | 583725 | 7 |
    +---------------------+------------------+---------------------+-----+

    I've tried without success this query

    mysql> SELECT
        A.*
    FROM
        `tbl_register` A
    INNER JOIN (
        SELECT
            sRegisterAccount,
            MAX(sDateTime) LastDatetimeForNode
        FROM
            `tbl_register`
        GROUP BY
            sRegisterAccount
    ) B ON A.sRegisterAccount = B.sRegisterAccount
    AND A.sDateTime = B.LastDatetimeForNode;
    
    +---------------------+------------------+---------------------+-----+
    | sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
    +---------------------+------------------+---------------------+-----+
    | 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
    | 2017-08-05 14:01:00 | 030270           | 116019              |   2 |
    | 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
    | 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
    | 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
    | 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
    | 2016-02-24 11:37:33 | 052994 | 583725 | 7 | +---------------------+------------------+---------------------+-----+ 6 rows in set

    what am I doing wrong?

    please, can you help me?

    expected results

    +---------------------+------------------+---------------------+-----+
    | sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
    +---------------------+------------------+---------------------+-----+
    | 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
    | 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
    | 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
    | 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
    | 2016-02-24 11:37:33 | 052994 | 583725 | 7 | +---------------------+------------------+---------------------+-----+

    it should also extract the row number 7, because the user have changed username from 052994 to 583725… but no longer authenticated with new username 583725

    structure table tbl_register below

    DROP TABLE IF EXISTS `tbl_register`;
    CREATE TABLE `tbl_register` (
      `sDateTime` datetime DEFAULT NULL,
      `sRegisterAccount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      `sNewRegisterAccount` varchar(255) DEFAULT NULL,
      `sID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`sID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of tbl_register
    -- ----------------------------
    INSERT INTO `tbl_register` VALUES ('2019-11-27 18:52:00', '116019', null, '1');
    INSERT INTO `tbl_register` VALUES ('2017-08-05 14:01:00', '030270', '116019', '2');
    INSERT INTO `tbl_register` VALUES ('2020-04-01 14:36:00', '542116', null, '3');
    INSERT INTO `tbl_register` VALUES ('2018-10-30 18:36:00', '000647', '542116', '4');
    INSERT INTO `tbl_register` VALUES ('2019-03-07 14:19:00', '115286', null, '5');
    INSERT INTO `tbl_register` VALUES ('2020-02-17 23:16:00', '170134', null, '6');
    INSERT INTO `tbl_register` VALUES ('2016-02-24 11:37:33', '052994', '583725', '7');
    Thursday, May 28, 2020 8:32 AM

Answers

  • User372437940 posted

    Hi,

    Try:

    SELECT
        A.*
    FROM
        `tbl_register` A
    INNER JOIN (
        SELECT
            sRegisterAccount,
            MAX(sDateTime) LastDatetimeForNode
        FROM
            `tbl_register`
        GROUP BY
            sRegisterAccount
    ) B ON A.sRegisterAccount = B.sRegisterAccount
    AND A.sDateTime = B.LastDatetimeForNode
    WHERE
        A.sNewRegisterAccount is null or
        not exists (select 1 from `tbl_register` E
                    where E.sRegisterAccount = A.sNewRegisterAccount)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 28, 2020 12:34 PM