MySQL INNER JOIN Select only one row from other table

Question

I have been trying to figure out a MySQL statement to perform the following.

I have a table with that stores jobs (tbl_jobs).
Another table that stores work scheduling (tbl_schedule) in the form of fixed time slots.
I want the resulting query to show all jobs scheduled today, check if the jobs are already scheduled (timeslot field) and return the earliest time slot.
My timeslots are stored as numbers from 1-8 so I used MIN to get the smallest number.
- There can be the same job spanning multiple timeslots.

I tried a code from MySQL INNER JOIN select only one row from second table but I believe I don't understand the query in depth enough to make my own statement for my purposes

 SELECT a.*, c.*
      FROM tbl_jobs a
      INNER JOIN tbl_schedule c
      ON a.job_id =  c.job_id
           INNER JOIN (
           SELECT job_id, MIN(timeslot) ts
           FROM tbl_schedule 
           GROUP BY job_id
           ) b ON c.job_id = b.job_id
      WHERE date = '2018-01-05'

This query on returns jobs that are scheduled and the ones that are not scheduled do not show up at all.

Would appreciate if anyone can assist me in where I should go from here? I am at a roadblock so, I decided to post here for help! Thanks in advance!

You need to use left join to get the unscheduled job – Mittal Patel Jan 05 '18 at 14:04 — Mittal Patel, Jan 05 '18 at 14:04

score 1 · Answer 1 · answered Jan 05 '18 at 14:05

To get unscheduled job, use the left join

  SELECT a.*, c.*
  FROM tbl_jobs a
  LEFT JOIN tbl_schedule c
  ON a.job_id =  c.job_id
       LEFT JOIN (
       SELECT job_id, MIN(timeslot) ts
       FROM tbl_schedule 
       GROUP BY job_id AND
       ) b ON c.job_id = b.job_id
  WHERE date = '2018-01-05'

MySQL INNER JOIN Select only one row from other table

1 Answers1