MySQL - Many to Many Join. Left Join already Present

Question

I am building a Attendance System for a Client and have become a tad stuck with JOIN's. I already have a left JOIN running, and want to add a JOIN on a many-to-many relationship but can't get my head around it.

The Problem:

I have a query that checks if a student is currently in a class, if they are it brings back the Student_ID, Class_ID and Attendance Status E.G Late, Present or Absent.

This query runs fine *IF the student is currently in a class, if they are not it returns an empty set. *

What I would like it to return is the student ID even if the student is in no class.

This is my query:

SELECT  student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_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 Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp
          AND Student.student_id like '1'

I think it needs a Left Outer Join but I can't seem to get my head around how to format the query correctly.

I have tried the below query but doesn't work!!!

SELECT  student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT OUTER JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id
                                                AND Student.student_id like '1')
          LEFT OUTER JOIN class ON(class.class_id = student_has_class.class_id)
          INNER JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp;

Any help would be very much appreciated.

There is a many to many relationship between: Student and Class called Student_Has_Class

Answer 1

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON:

SELECT  DISTINCT student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
          LEFT JOIN class ON class.class_id = student_has_class.class_id
            AND NOW() between class.class_start_timestamp and class.class_end_timestamp
          LEFT JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE Student.student_id like '1'

And please no like '1', it's = '1', but probably student_id is numeric so = 1