php - MySQL - JOIN Operation Multiple Tables -

i creating online attendance system, , problem around query below not bringing correct data.

i looking bring student id, class id , attendance status e.g present late or absent of class.

if attendance has been taken, query run's fine. however, if no attendance has been taken query returns empty set.

i looking still return data have 'null' or in 'attendance status' column, if attendance hasn't been taken.

this query:

and bring these results if attendance has been taken class:

however, if attendance hasn't been taken, return empty set. in attendance column print 'null' or make sure data still comes back.

at minimum still need class , student id. think may need join, can't head around it.

you should use proper syntax of joins! can cause lot of problems , harder understand.

what need left join can used using(+) after condition of table want left join - don't it.

your query should be:

 select class.class_id 'class_id',         student.student_id 'student_id',         case when attendence.attendence_status null 'not present' else attendence.attendence_status end 'attendence_status' student inner join student_has_class on(student_has_class.student_id = student.student_id) inner join class on(class.class_id = student_has_class.class_id) inner join subject on(class.subject_id = subject.subject_id) left join attendence on(attendence.class_id = class.class_id                         , attendence.student_id = student.student_id) now() between class.class_start_timestamp , class.class_end_timestamp , student.student_id '1'