Finding missing records

[Donovan]

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.

 

SELECT  * FROM atlas_tl_students a
JOIN atlas_tl_session_grade_import b ON ( a.UID = b.Student_ID ) 
JOIN atlas_tl_session c ON (b.Session_ID = c.Session_ID)
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

 

I tried a LEFT JOIN from

atlas_tl_students a LEFT JOIN atlas_tl_session_grade_import b

  but this didn't work either.

 

Something I am doing is not working.  I erased a student record from the imported table but have yet to get any value returned.

 

Once I get a return

 

if ( $missingStudentTotal > 0 ) {

 

then I will do something.

[Donovan]

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 

[Donovan]

Can anybody take a look at this?

[Azarian]

I think your making this hard than it has to be or maybe I am confused about what you really want.

 

First off if you imported from CSV file why would there be any missing records?

 

Second all this info is in 1 table?

[Donovan]

I have no way of knowing if every students for that class was present during the exam, until I join it back to the students table to see if I have missing records.

 

My student table is atlas_tl_students

 

My imported table from the csv is atlas_tl_session_grade_import

 

I join these two on ( a.UID = b.Student_ID )

 

Then I join the Session_ID  between atlas_tl_session and atlas_tl_session_grade_import ( b.Session_ID = c.Session_ID )

 

Then I join the Course_ID between atlas_tl_courses and atlas_tl_session ( c.Course_ID = d.Course_ID )

 

Then I compare where the Class_Year for a student (first year or second year students, ie 1 or 2) match the Course_Year (1 or 2)

 

WHERE d.Course_Year = a.Class_Year AND b.Student_ID IS  NULL

 

And finally where I have missing Student_ID's