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