SQL Query with multiple JOINS on multiple tables

Question

I have created this query to match data against multiple tables but i feel that it has got a little complicated.

I need to select from the following tables:

• leads
• instructors_options
• instructor_calendar

using the following relationships:

leads.sequence = instructors_options.instructor_seq
leads.sequence = instructor_calendar.instructor_seq

and the following filters MUST match:

• lead.lead_type = 'Instructor'
• instructors_options.option_name = 'pupil_cap' AND instructors_options.value > 0
• instructors_options.option_name = 'car_type' AND instructors_options.value = 'variable'
• instructors_options.option_name = 'areas_covered' AND instructors_options.value = 'variable'
• instructor_calendar.pupil_name = 'AVAILABLE' AND instructor_calendar.date >= 'variable' AND instructor_calendar.date <= 'variable'

• instructors_options.option_name = 'diary_updates' AND instructors_options.value = '1'

    SELECT    i.sequence as instructor_seq, 
                  ic.date AS date, 
                  ic.start_time AS start_time, 
                  ic.end_time AS end_time
          FROM 
              leads i 
  
          LEFT JOIN 
              instructors_options io 
          ON 
              i.sequence = io.instructor_seq AND 
              (io.option_name = 'pupil_cap' AND io.value > '0') 
  
          RIGHT JOIN 
              instructors_options io2 
          ON 
              i.sequence = io2.instructor_seq AND 
              io2.option_name = 'car_type' AND io2.value = '".$bookingData["car_type"]."' 
  
          RIGHT JOIN 
              instructors_options io3 
          ON 
              i.sequence = io3.instructor_seq AND 
              io3.option_name = 'areas_covered' AND (io3.value LIKE '".substr($postcode, 0, 1)."' OR io3.value LIKE '".substr($postcode, 0, 2)."' OR io3.value LIKE '".substr($postcode, 0, 3)."' OR io3.value LIKE '".substr($postcode, 0, 4)."')
  
          RIGHT JOIN 
              instructors_options io4 
          ON 
              i.sequence = io4.instructor_seq AND 
              io4.option_name = 'diary_updates' AND io4.value = '1' 
  
          RIGHT JOIN 
              instructor_calendar ic 
          ON 
              i.sequence = ic.instructor_seq AND 
              ic.pupil_name = 'AVAILABLE' AND 
              ic.date >= '".ChangeDateFormat($date, 'Y-m-d')."' AND 
              ic.date <= '".date('Y-m-d', strtotime($date. ' + 7 days'))."' 
          WHERE 
              i.lead_type = 'Instructor' 
          GROUP BY date 
          ORDER BY date ASC, start_time ASC
  

Can someone please help me update my query to ensure I have done it correctly

Thank you!

Answer 1

You are looking for certain calendar entries. So select from that table.

One of the conditions is that the related instructor sequence has all of four certain options. So aggregate the options per instructor sequence and keep those who match all criteria. Use an IN clause to get the calendar entries for these instructor sequences.

select 
  instructor_seq,
  date, 
  start_time, 
  end_time
from instructor_calendar 
where pupil_name = 'AVAILABLE' 
and date >= '2017-01-01' 
and date <= '2017-05-31'
and instructor_seq in
(
  select instructor_seq
  from instructors_options
  group by instructor_seq
  having sum(option_name = 'pupil_cap'     and value > 0)   > 0
     and sum(option_name = 'car_type'      and value = 123) > 0
     and sum(option_name = 'areas_covered' and value = 456) > 0
     and sum(option_name = 'diary_updates' and value = 1)   > 0
);

The HAVING clause makes use of MySQL's true = 1, false = 0 by the way.