Unable to get data from nested SQL query

Question

const getCompanyShifts = (req, res) => {
  try {
    const { company_id } = req.params;
    connection.query(
      `SELECT * FROM jobs WHERE company_fk=${company_id}`,
      (err, rowss) => {
        if (!err) {
          connection.query(
            `SELECT * FROM shift WHERE isBooked=1 AND fk_job = ?`,
            [rowss.jobsID],
            (err, rows) => {
              if (err || rows.length === 0) {
                res.status(404).json({
                  success: false,
                  message: "Company Shifts Not Found!",
                  err,
                });
              } else {
                const shifts = [];
                rows.forEach((row, i) => {
                  const shift = {
                    shiftID: rows[i].shiftID,
                    shiftStartTime: rows[i].startTime,
                    shiftEndTime: rows[i].endTime,
                    shiftDate: rows[i].date,
                    isBooked: rows[i].isBooked,
                    fk_job: rows[i].fk_job,
                    fk_guard: rows[i].fk_guard,
                  };
                  shifts.push(shift);
                });
                res.status(200).json({
                  success: true,
                  message: "Successfully Retrieved Company Shifts!",
                  shifts,
                });
              }
            }
          );
        } else {
          res.status(404).json({
            success: false,
            message: "Company Jobs Not Found!",
          });
        }
      }
    );
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

In the first query of the above code, I get all the rows from the jobs table. In the second nested query, I am trying to get all rows from the shift table for each jobsID returned by the first query. But I don't get any data. The data is there and it should return the data but I'm not getting any data. What am I doing wrong here? please help!

Answer 1

I think there is a misunderstanding of how the data is returned and how the second query works. According to you:

You will return multiple rows. So the first query works. But retrieving multiple rows will result in rowss becoming array. So the rowss.jobsID used as input for the next query is not the correct use of an array, I want the value of that expression to be undefined which will cause the second query to not return Any content.

To prove it add console.log(rowss) like this:

[...]
connection.query(
  `SELECT * FROM jobs WHERE company_fk=${company_id}`,
  (err, rowss) => {
    console.log(rowss);
[...]

To solve this problem, I recommend using the sql function and issuing join. By doing this, the database will join the two tables and then return only the rows that satisfy the where condition. The combined statement should look like this:

SELECT * FROM jobs WHERE company_fk=${company_id} LEFT JOIN shift ON shift.fk_job = jobs.jobsID WHERE isBooked=1

Tip: Depending on ob's and shift's database schema, you may need to expand * and list all table names explicitly, such as SELECT jobs.jobsID, jobs.<xyz>, shift.isBooked, shift.fk_job [...] FROM [...]. If you have columns with the same name in both tables, you may need to resolve conflicts caused by join while combining the columns to return results, like this: SELECT [...] shift. <xyz> as shift_xyz [. ..]from...[].

As an added bonus, you also only need one SQL query instead of two.