[FIXED] MySQL query missing results in node-mysql that are returned in phpMyAdmin

Issue

My SQL query returns four columns when I run it in phpMyAdmin, but only two when performed through Node.js and the mysql package. I suspect it might have to do with the asynchronicity or with LEFT JOIN, but I can't figure it out.

Node index.js:

app.get('/name', function (req,res){
  var query='SELECT s.result, s.distance, hp.result, hp.distance FROM `table15` s LEFT JOIN `table14` hp on s.name = hp.name WHERE s.name = "John Appleseed"';
  pool.query(query,function(err, result) {
    if (err) throw err;
    console.log(result);
    res.json(result);
  });
});

app.js

  $.ajax({
      url : "/name",
      type : "GET",
      success : function(data){
          var len = data.length;
          console.log(data);
      }
  });

Returns in browser and console:

[{result: "36:24", distance: 12}]

But in phpMyAdmin I get four columns and would therefor expect something like this in the browser as well:

[{result: "00:35:29", distance: 12, result: "36:24", distance: 12}]

Solution

Having zero familiarity with node.js i would suggest the following. Try altering your query and add names to your result columns.

Example

SELECT `s`.`result` AS `sResult`, 
`s`.`distance` AS `sDistance`, 
`hp`.`result` AS `hpResult`, 
`hp`.`distance` AS `hpDistance` FROM `table15` `s` 
LEFT JOIN `table14` `hp` on `s`.`name` = `hp`.`name` 
WHERE `s`.`name` = "John Appleseed";

The above should output

[{sResult: "00:35:29", sDistance: 12, hpResult: "36:24", hpDistance: 12}]

Answered By - user2560539