i want to select schedule of specific teacher for a given day .... statement should assume that a teacher may teach in elementary and high school

here is my statement

   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


output of query

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.

  • 3
    Shouldn't you have a condition that joins the time_range with some other table? – JoseTeixeira Sep 4 '14 at 8:56
  • @user16484 if i do that it will result will only show 1 row. expected result should be 5 rows. only 1 row will have all data. other 4 will have null data – Ronald Manlapao Sep 5 '14 at 8:06
  • You are mixing the explicit JOIN operator with implicit joins in the WHERE clause. That is a really bad idea (and any other DBMS would simply reject the invalid SQL). The condition for a JOIN operator needs to be defined using the ON keyword right after the JOIN. Plus: your group by is invalid as well (and again every other DBMS would reject the invalid statement). For details see: mysqlperformanceblog.com/2006/09/06/… – a_horse_with_no_name Sep 5 '14 at 11:53

When you want only one row you have to group your data. As you are looking for infos of one day a GROUP on a date seems to do the trick. In your case GROUP BY day_name if you don't have a date field.

And when you group you can use MIN and MAX for your time: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

  • it still does not show the expected results ... result is just one row. in my example result should have 5 rows but other rows should have null values – Ronald Manlapao Sep 5 '14 at 8:00
  • Did i misread your statement "expected result - one row (last row) "? Do you want to have a single row (grouping) or still 5 rows but only in the newest row show the time information? (subselect should work) I would prefer to clear the output data in your programming language than to run several subselects on the data. – Rossi Sep 5 '14 at 14:07
  • 5 rows but other rows should have null data on columns subject_classmodel_name, subject_teachers_name, subject_section_name, subject_ref, time_ref and day_ref – Ronald Manlapao Sep 5 '14 at 15:29
  • When you do it by program (php, c++ ...) you get the last record first and remember value of time_time_start . "-" . time_time_end and check for this flag-value when you rewind to starts and print/compute your data. I presume thats the best, resource preserving way. Otherwise look for the JOIN answer from user16484 for a pure SQL solution. For myself i would work with subselects btw. with an insert into in a memory based (temp) table on which you could do a fast select with a subselect to get the data and check against the max value. – Rossi Sep 10 '14 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.