How to retrive data from multiple tables. Using node js and mysql?

Question

I have two tables which are give in at this fiddle.

I want to retrieve data from the table currently I am doing this way

mysqlConnection.query("SELECT* FROM wallet WHERE user_id = ? ORDER by date",authData.id,(err,rows,fields)=>{
  if(!err){
    res.json({
      message :"Statement",
      rechargeDetails : rows,
      transactionDetail://// TODO: Transaction details

    });
  }
  else console.log(err);
});

That is correctly retrieving the JSON data in at this format over here

{
    "message": "Statement",
    "rechargeDetails": [
        {
            "id": 17,
            "amount": 50,
            "type": "CREDIT",
            "pg_id": "xxxxq123",
            "comments": null,
            "intrument_type": "REFUND",
            "user_id": 1,
            "date": "2020-04-17T18:30:00.000Z"
        },
        {
            "id": 18,
            "amount": 50,
            "type": "CREDIT",
            "pg_id": "xxxxq123",
            "comments": null,
            "intrument_type": "REFUND",
            "user_id": 1,
            "date": "2020-04-17T18:30:00.000Z"
        }
    ]
}

I want to get transaction table data also like this

{
    "message": "Statement",
    "rechargeDetails": [
        {
            "id": 17,
            "amount": 50,
            "type": "CREDIT",
            "pg_id": "xxxxq123",
            "comments": null,
            "intrument_type": "REFUND",
            "user_id": 1,
            "date": "2020-04-17T18:30:00.000Z"
        },
        {
            "id": 18,
            "amount": 50,
            "type": "CREDIT",
            "pg_id": "xxxxq123",
            "comments": null,
            "intrument_type": "REFUND",
            "user_id": 1,
            "date": "2020-04-17T18:30:00.000Z"
        }
"transactionDetails": [
        {
            "id": 17,
            "amount": 50,
            "tax": "CREDIT",
            "discount": "xxxxq123",
            "adjustment": null,
            "refrence_id": "REFUND",
            "payment_ref_id": 1,
           xxxxxx
           xxxxxxxxxx
        }
    ],

}

How could i do that? Is there a way to do this?

Surojit Paul · Accepted Answer · 2020-04-18 14:36:36Z

mysqlConnection.query("SELECT * FROM wallet WHERE user_id = ? ORDER by date",authData.id,(err,rows,fields)=>{
  if(!err){
      mysqlConnection.query("SELECT * FROM transaction WHERE user_id = ? ORDER by date",authData.id,(errTarn,rowsTarn,fieldsTarn)=>{
          if(!errTarn){
              res.json({
                message :"Statement",
                rechargeDetails : rows,
                transactionDetail: rowsTarn

             });
          }
      }); 

  }
  else console.log(err);
});

OR YOU CAN USE MULTIPLE QUERY

enable it for your connection

var connection = mysql.createConnection({multipleStatements: true});

Now the code will be

connection.query('SELECT * FROM wallet WHERE user_id = ? ORDER by date; SELECT * FROM transaction WHERE user_id = ? ORDER by date', [authData.id, authData.id], function(err, results) {
  if (err) throw err;
  if(!err){
      res.json({
            message :"Statement",
            rechargeDetails : results[0],
            transactionDetail: results[1]

      });
  }
});

Ahmed Moayad AlMoghrabi · Accepted Answer · 2020-04-18 12:55:50Z

0

I have zero knowledge with NODE but I think something like this should work properly

mysqlConnection.query("SELECT* FROM wallet WHERE user_id = ? ORDER by date",authData.id,(err,rows,fields)=>{
    if(!err){
        res.json({
            message :"Statement",
            rechargeDetails : rows,
            transactionDetail: mysqlConnection.query("__SELECT_TRANSACTIONS_QUERY__",authData.PARAMETER_NEEDED,(err,rows,fields)=>{
                if(!err){
                    // return your rows
                }
            });
        });
    } else {
        console.log(err);
    }
});

answered Apr 18, 2020 at 12:55

Ahmed Moayad AlMoghrabi

1,2872 gold badges12 silver badges20 bronze badges

2 Comments

arun VM Over a year ago

This helped me technicalkeeda.com/nodejs-tutorials/…

Ahmed Moayad AlMoghrabi Over a year ago

Ok this is great 👍

Collectives™ on Stack Overflow

How to retrive data from multiple tables. Using node js and mysql?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related