mysql query works in phpmyadmin but not in node.js

Question

I have a query like this...

SELECT *
FROM `000027`,`000028`
WHERE `000027`.id=(SELECT max(`000027`.id) FROM `000027`)
AND `000028`.id=(SELECT max(`000028`.id) FROM `000028`)

which returns something like this in phpmyadmin...

id time value id time value

However, in react.js it is only returning one of these like this...

id time value

2 questions, Why is it doing this? and, how can I get it to return both instead of one?

my node.js code...

const sqlSelect = "SELECT * FROM `000027`,`000028` WHERE `000027`.id=(SELECT max(`000027`.id) FROM `000027`) AND `000028`.id=(SELECT max(`000028`.id) FROM `000028`)"
dbPlant.query(sqlSelect, (err, result) => {
    console.log(result)
    res.send(result)
    res.end()
})

and it sends this back with only one rowdatapacket when it should be two, or two of each of those values...

[
  RowDataPacket {
    id: 652,
    time: 2021-01-24T17:28:01.000Z,
    value: '262'
  }
]

question from:https://stackoverflow.com/questions/65874875/mysql-query-works-in-phpmyadmin-but-not-in-node-js

Answer 1

Your two tables have some column names in common. This is okay to have repeated column names in a result set in the mysql client, but some programming interfaces map a rows of a result set into a hash array, where the column names are the keys. So if you have duplicate column names, one naturally overwrites the other.

The remedy is to define column aliases for one or the other of each duplicate, so they are mapped into distinct keys in the result set.

You must do this one column at a time. Sorry, you can't use SELECT * anymore (you shouldn't use SELECT * anyway). There is no "automatic alias all columns" option.

SELECT 
  `000027`.id AS id27,
  `000027`.time AS time27,
  `000027`.value AS value27,
  `000028`.id AS id28,
  `000028`.time AS time28,
  `000028`.value AS value28
FROM `000027`,`000028`
WHERE `000027`.id=(SELECT max(`000027`.id) FROM `000027`)
AND `000028`.id=(SELECT max(`000028`.id) FROM `000028`)