Cannot 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 am getting 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 of the jobsID returned from the 1st query. But i don't get any data back. The data exists and it should return data but i don't get any data back. What am i doing wrong here ? Please help!

mottek · Accepted Answer · 2022-07-17 21:25:58Z

1

i assume there is a misunderstanding on how the data gets returned and therefore how the second query would work. According to your statement:

[...] i am getting 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 of the jobsID returned from the 1st query. But i don't get any data back.

You are getting multiple rows back. So the first query works. But getting multiple rows back would result in rowss being an array. Therefore rowss.jobsID which is used as input for the next query isn't a correct use of an array and i expect the value of that expression to be undefined which will then result in the second query not returning anything.

To prove that add console.log(rowss) like so:

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

To solve the issue, i suggest to use sql capabilities and issue a join. By doing so, the Database will join the two tables, then returning only rows that fulfill the where condition. The combined statement should look like:

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

Hint: Depending on your DB schemes for ob'sand shift you might need to expand the * and list all table names explicitly, e.g. SELECT jobs.jobsID, jobs.<xyz>, shift.isBooked, shift.fk_job [...] FROM [...]. If you have column with same name in both tables, you might need to resolve the conflict which is caused by the join while combining the columns for returning the results like so: SELECT [...] shift.<xyz> as shift_xyz [...] FROM ...[].

As a plus, you also just need one SQL query instead of two.

edited Jul 17, 2022 at 21:25

answered Jul 17, 2022 at 21:18

mottek

9675 silver badges12 bronze badges

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

3 Comments

Mushood Hanif Over a year ago

Your solution is correct, but the issue is that for the JOIN query i need to have some way to link shift and company together. But the database doesn't allow that. This is the reason i chose to go this route. I understand that rowss returns array which is why i wrote the 2nd query that way. But it doesn't work. I need this method to work. Could you guide me accordingly?

mottek Over a year ago

Yes. Would you mind to elaborate But the database doesn't allow that ? You didn't mention a table with name company in the question. I am sure there is a way to join jobs, company and shift to query the data you need using a single query.

Mushood Hanif Over a year ago

i updated the database to support this JOIN query, it works now! Thanks for the solution.

Collectives™ on Stack Overflow

Cannot Get Data From Nested SQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related