MySQL Forums
Forum List  »  General

Re: mysql join not working as expected
Posted by: Peter Brawley
Date: September 08, 2014 09:12AM

It's much easier to debug and maintain a query when you write recommended join syntax, ie a join b on a.x=b.y. When we rewrite your query that way ...

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 on class_teachers_section.id = sched.subject_ref
join time_range             on time_range.level = "elementary"                # one-sided join
join day                    on day.id = sched.day_ref                         
join class                  on class.id = class_teachers_section.class_ref 
join teachers               on teachers.id = class_teachers_section.teachers_ref 
                               and teachers.name = "ronald manlapao"   
join section                on section.id = class_teachers_section.section_ref 
                               and section.level = "elementary" 
order by time_range.time_start asc ;

... we find that your join to the time_range table.is one-sided, ie it specifies nothing for the loeft side of the join. Is that intended?

If fixing that doesn't solve the problem you describe, you need to debug the query one join at a time, ie start with the first join, impose a Where condition that makes the resultset workably small, and verify that it's retrieving what's expected, then add in each remaining join in turn, one at a time, and verify, till you find the join that's misbehaving.

Options: ReplyQuote


Subject
Written By
Posted
Re: mysql join not working as expected
September 08, 2014 09:12AM


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.