mysql join not working as expected
i want to select schedule of specific teacher for a given day .. query should show teachers schedule for the day (some row may have null columns) .... statement should assume that a teacher may teach in elementary and high school
here is my statement
select
class.name as subject_classmodel_name,
teachers.name as subject_teachers_name,
section.name as subject_section_name,
day.name as day_name,
time_format(time_range.time_start,'%H:%i') as time_time_start,
time_format(time_range.time_end,'%H:%i') as time_time_end,
sched.subject_ref, sched.time_ref, sched.day_ref
from schedule sched
join class_teachers_section
join time_range
join day
join class
join teachers
join section
where teachers.name = "ronald manlapao" and
section.level = "elementary" and
time_range.level = "elementary" and
class_teachers_section.id = sched.subject_ref and
class.id = class_teachers_section.class_ref and
teachers.id = class_teachers_section.teachers_ref and
section.id = class_teachers_section.section_ref and
day.id = sched.day_ref
order by time_range.time_start asc
class_teachers_section (table) has tables class, teachers, section schedule (table) has tables class_teachers_section, day, time_range
result: 5 rows but all columns have values
subject_classmodel_name subject_teachers_name subject_section_name day_name time_time_start time_time_end subject_ref time_ref day_ref
PE ronald manlapao grade 6 TUESDAY 07:00 08:00 1 4 2
PE ronald manlapao grade 6 TUESDAY 09:00 10:00 1 4 2
PE ronald manlapao grade 6 TUESDAY 10:00 11:00 1 4 2
PE ronald manlapao grade 6 TUESDAY 13:00 14:00 1 4 2
PE ronald manlapao grade 6 TUESDAY 14:00 15:00 1 4 2
expected result
only one row (last row) with time_start (14:00) time_end (15:00) should have data in columns subject_classmodel_name, subject_teachers_name, subject_section_name, subject_ref, time_ref and day_ref. the rest should be null
Im using MYSQL 5.6.