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!

Answer 1

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'