When username changes on table register get only last username in mysql [Answered]RSS

1 reply

Last post May 28, 2020 12:34 PM by gapimex

‹ Previous Thread|Next Thread ›

Shortcuts

Chevy Marl S...

Member

43 Points

66 Posts

When username changes on table register get only last username in mysql

May 28, 2020 08:32 AM|Chevy Marl Sunderland|LINK

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');

gapimex

Member

310 Points

76 Posts

Re: When username changes on table register get only last username in mysql

May 28, 2020 12:34 PM|gapimex|LINK

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

Signature: http://www.imoveisemexposicao.com.br/imoveis-alugar-guarulhos-comercial_ou_industrial-galpão

‹ Previous Thread|Next Thread ›