3.4.3: Nested SQL Queries

Search…
In order to query related data across multiple tables we need multiple queries. For example, if we have 2 tables recipes and categories as per the schema in 3.4.2: SQL Relationships (One to Many), and we wish to query for the category name of a given recipe, we would employ 2 queries to do so. The 1st query would query for the category_id within the given recipe's record, and the 2nd query would query the categories table for that category_id's record.
Sample recipes and categories CREATE TABLE statements
CREATE TABLE recipes (id SERIAL PRIMARY KEY, label TEXT, category_id INTEGER);
CREATE TABLE categories (id SERIAL PRIMARY KEY, name TEXT);
Sample index.js
Because of the asynchronous nature of a SQL query (because it happens over the network), we have to nest the 2nd query inside the first. This is so that the 2nd query only runs after the 1st query has returned its result.
import pg from 'pg';
const { Client } = pg;
​
// set the way we will connect to the server
const pgConnectionConfigs = {
  user: '<MY_UNIX_USERNAME>',
  host: 'localhost',
  database: '<MY_UNIX_USERNAME>',
  port: 5432, // Postgres server always runs on this port
};
​
// create the var we'll use
const client = new Client(pgConnectionConfigs);
​
// make the connection to the server
client.connect();
​
// write the SQL query
const recipeQuery = 'SELECT * from recipes WHERE id=1';
​
// run the SQL query
client.query(recipeQuery, (recipeQueryError, recipeQueryResult) => {
  // this error is anything that goes wrong with the query
  if (recipeQueryError) {
    console.error('recipe query error', recipeQueryError);
    client.end();
    return;
  }
​
  // rows key has the data
  console.log(recipeQueryResult.rows);
​
  // return early if no results
  if (recipeQueryResult.rows.length <= 0) {
    console.log('no results!');
    client.end();
    return;
  }
​
  // extract the recipe that we queried for
  const recipe = recipeQueryResult.rows[0];
​
  // MAGIC: use the result of the 1st query in the 2nd
  const categoryQuery = `SELECT * FROM categories WHERE id=${recipe.category_id}`;
​
  client.query(categoryQuery, (categoryQueryError, categoryQueryResult) => {
    // this error is anything that goes wrong with the query
    if (categoryQueryError) {
      console.error('category query error', categoryQueryError);
      client.end();
      return;
    }
​
    // rows key has the data
    console.log(categoryQueryResult.rows);
​
    // close the connection
    client.end();
  });
});
Note how we have to make sure names in the second query callback such as the result and error parameters don't overlap with the first query callback parameters.
3.4.2: PostgreSQL Node App
3.4.4: PostgreSQL Express App
Last modified 3mo ago
Copy link