NodeJS mysql fetching related data

Question

In my database I have two tables. Let's call them A and B. B table has foreign keys related to ids of records in A table.

I fetch couple of ids from A table and I want to find records in B table with foreign keys matching to those ids. However not every record in A table has records related to it in B table.

So in my code it looks like this:

var idsFromA = [1, 2, 3, 4, 5]

connection.query("SELECT * FROM B_table WHERE a_id = ?", idsFromA, function(err, results) {
  if (err) {
    return console.log(err)
  } else {
    // always empty
    console.log(results);
  }
});

Let's say ids number 1 and 2 from table A has record related to them in table B. Rest of them not. I want to get records from table B with those foreign keys but I receive only an empty array. It works only if I type query with only one matching value. But for the whole array of values where some of them don't have related record not.

How can I fix it and get information about those record?

Will there be always only one entry in B for each A, or could there be more then one entry in B for each A? — t.niese
– t.niese, Commented Jul 19, 2017 at 7:13

t.niese · Accepted Answer · 2017-07-19 07:31:48Z

1

Instead of = you have to use IN and you need to pass the array as value for the first placeholder.

connection.query("SELECT * FROM B_table WHERE a_id IN (?)", [idsFromA], ...)

The way you wrote it only the first id in the array idsFromA would be used for the ?.

Instead of using two queries you might want to use a LEFT JOIN, and the nestTables option.

connection.query({
  query: 'SELECT * FROM A_table LEFT JOIN B_table ON (A_table.id=B_table.a_id) WHERE ...some condition to filter A...',
  nestTables: true
}, function(err, results) {
  if (err) {
    console.log(err)
  } else {
    console.dir(results);
  }
});

edited Jul 19, 2017 at 7:31

answered Jul 19, 2017 at 7:21

t.niese

41k9 gold badges78 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

NodeJS mysql fetching related data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related