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!

Is this a MySQL question, or a SQLServer question? These are two VERY different products, and you have them both tagged. Why? — pmbAustin
– pmbAustin, Commented Jun 5, 2017 at 20:30
Sorry - my mistake, must have accidentally clicked sql-server — charlie
– charlie, Commented Jun 5, 2017 at 20:32
The first thing I notice is that you are looking for "MUST" matches, but have LEFT and RIGHT joins. Are you sure you don't want INNER JOIN? And you either have an extra join (io4) or you missed that in the requirements list :) — Jacob H
– Jacob H, Commented Jun 5, 2017 at 20:35
Can you explain the cardinality between the tables? I think it's Leads(1)-<(0)Instructors_options and Leads(1)-<(0) w/ instructor calendar... but do calendars relate to options at all? or is this a true cross join? — xQbert
– xQbert, Commented Jun 5, 2017 at 20:42

Thorsten Kettner · Accepted Answer · 2017-06-05 21:09:34Z

2

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.

edited Jun 5, 2017 at 21:09

answered Jun 5, 2017 at 20:58

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

3 Comments

charlie Over a year ago

ok, i can give this a test. another thought just now, currently this is searching between the selected date and the selected date + 7 days. if no results are returned, i run exactly the same query again but change the date filters. Can i run this all in one query so if no results are returned, it will remove the second date<= clause?

Thorsten Kettner Over a year ago

In standard SQL you would simply select both weeks, then rank your records by week with ROW_NUMBER and keep the best ranked week. MySQL lacks this function. You'd usually emulate this with variables in MySQL.

charlie Over a year ago

ok, scrap my last comment. i'll keep rerunning the query if no results are found. I need to loop through the dates initially then the time for each date. is the best way to run another query inside a loop?

Collectives™ on Stack Overflow

SQL Query with multiple JOINS on multiple tables

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