MySQL Forums
Forum List  »  General

mysql join not working as expected
Posted by: ronald manlapao
Date: September 07, 2014 07:29PM

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.

Options: ReplyQuote


Subject
Written By
Posted
mysql join not working as expected
September 07, 2014 07:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.