MySQL - 多對多連接。離開加入已經存在

我正在為一個客戶建立一個考勤系統，並且已經變得有點卡在 JOIN 上了。我已經有一個左 JOIN 正在執行，並且想在多對多關係上添加一個 JOIN，但我無法理解它。

問題：

我有一個查詢，檢查學生目前是否在課堂上，如果是，則返回Student_ID、Class_ID和出勤狀態EG Late、Present 或 Absent。

此查詢執行良好如果學生目前在課堂上，如果不是，則返回一個空集。

我希望它返回的是學生證，即使學生沒有上課。

這是我的查詢：

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'

我認為它需要一個左外連接，但我似乎無法理解如何正確格式化查詢。

我已嘗試以下查詢，但不起作用！

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;

任何幫助將不勝感激。

之間存在多對多關係：Student和Class稱為Student_Has_Class

根據經驗，外表上的條件通常放在 WHERE 中，但內表上的 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'

請不要like '1'，它是= '1'，但可能student_id是數字所以= 1

引用自：https://dba.stackexchange.com/questions/128643