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'
  }
]

Could you elaborate on what you mean by *However, in react.js it is only returning one of these like this...*. Specifically, how are you running this query in React? React is a front-end library and to my knowledge does not allow you work directly with a database. — codemonkey, Jan 24 '21 at 19:23
Sorry I meant node.js. Long day. I will put the query in an edit. — Justin Oberle, Jan 24 '21 at 19:25

Bill Karwin · Accepted Answer · 2021-01-24T19:34:37.737

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`)

Thank you!. I will give this a whirl. Makes sense – Justin Oberle Jan 24 '21 at 19:33 — Justin Oberle, Jan 24 '21 at 19:33

mysql query works in phpmyadmin but not in node.js

1 Answers1