MySQL - JOIN操作多个表

Question

I am creating an Online Attendance System, and my problem is around a query below not bringing back the correct data.

I am looking to bring back a Student ID, Class ID and the Attendance Status E.G Present Late or Absent of that class.

If attendance has been taken, then the query run's fine. However, if no attendance has been taken the query returns an empty set.

I am looking to still return all the data but have 'null' or anything in the 'Attendance Status' column, If attendance hasn't been taken.

This is the query:

And it bring back these results if attendance has been taken for the class:

However, If attendance hasn't been taken, then it will just return an empty set. What I would like is for in the attendance column it print 'null' or anything to make sure some data still comes back.

At minimum I still need the Class and Student ID. I think it may need a JOIN, I just can't get my head around it.

Answer 1

You should use the proper syntax of JOINS! that can cause a lot of problems and it is harder to understand.

What you need is a LEFT JOIN which can be used using(+) after the condition of the table you want to left join to - DON'T DO IT.

Your query should be:

 SELECT class.class_id as 'Class_ID',
        student.Student_ID as 'Student_ID',
        case when attendence.attendence_status is null then 'Not Present' else attendence.attendence_status end as 'attendence_status'
FROM 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
                        AND attendence.student_id = student.student_id)
WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp
AND Student.student_id like '1'