I have a table that is imported from a csv file.
I need to find any records that may be missing. The file in question are grades from a test. I need to find any missing students who may have missed class the day of the exam.
atlas_tl_session_grade_import has Student_ID
atlas_tl_students have UID that should match Student_ID's
I need to find only those students for the respective year they are in. (ie.. first years students, 2nd years students)
I do this by matching what Session_ID from what Course and then getting the Course_Year (ie , 1 or 2, or 3, or 4) to match the Class_Year of the students. This way I am only looking at students from the correct year rather than all 600 students from all years.
Then find any student that is missing.
This gives me about 101 records of all first year students.
SELECT *
FROM atlas_tl_students a
LEFT JOIN atlas_tl_session_grade_import b ON ( a.UID = b.Student_ID )
LEFT JOIN atlas_tl_session c ON ( b.Session_ID = c.Session_ID )
LEFT JOIN atlas_tl_courses d ON ( c.Course_ID = d.Course_ID )
WHERE d.Course_Year = a.Class_Year
This gives me nothing, yet I think it should give me those students who are missing from the import.
SELECT *
FROM atlas_tl_students a
LEFT JOIN atlas_tl_session_grade_import b ON ( a.UID = b.Student_ID )
LEFT JOIN atlas_tl_session c ON ( b.Session_ID = c.Session_ID )
LEFT JOIN atlas_tl_courses d ON ( c.Course_ID = d.Course_ID )
WHERE d.Course_Year = a.Class_Year AND b.Student_ID IS NULL
