1

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

1 Answer 1

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

4
  • Thank you for the advice - the query worked fine, apart from it returned the expected row 15 times, where it should of only returned it once. Do you know why it would do this? Its bringing back duplicate data. Commented Feb 8, 2016 at 21:13
  • @user3180997: E.g. one class might have multiple subjects, resulting in multiple rows returned. Adding DISTINCT should help.
    – dnoeth
    Commented Feb 8, 2016 at 21:28
  • I can't seem to get the DISTINCT working on the CASE, it doesn't seem to like the syntax, is there any chance you could update the answer if possible with the DISTINCT on the Class? Thank you. Commented Feb 8, 2016 at 21:56
  • @user3180997: There's only one place to write DISTINCT, next to SELECT. You might also remove the join to the SUBJECT, it's not used at all.
    – dnoeth
    Commented Feb 8, 2016 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.