Finding missing records

[SFDonovan]

I have been spinning my wheels for days on this and have tried 4 different ways with no success.

I have a table that I create and import the exam results from a csv file.

Here is the table(s) structure

The import table

session_grade_import
Student_ID varchar(9) NULL,
Name_Last varchar(50) NULL,
Name_First varchar(30) NULL,

Total_Percent tinyint(3) NULL,
Course_Year tinyint(4) NULL,
Session_ID int(11) NOT NULL

The student table

atlas_tl_students
UID varchar(9) default NULL,
Class_Year tinyint(4) default NULL,
Name_First text NOT NULL,
Name_Last text NOT NULL,
Name_Middle text,
SOMS_KEY int(11) NOT NULL default '0',

I need to find any first year students (example.. Class_Year = 1) from the student table that may have missed an exam (example..Session_ID = 45). I match the Class_Year to the Course_Year to pull any first years medical students who's UID does not exist in the imported table.

So I want to find all NULL values in the imported table, but so far have not been able to, even though I know I do have missing records.

My first attempt:

SELECT a.* 
FROM  atlas_tl_students a
LEFT  JOIN  atlas_tl_session_grade_import b ON ( b.Student_ID = a.UID ) 
WHERE a.Class_Year = b.Course_Year AND b.Student_ID IS  NULL;

Does not pull any records.

My second attempt:

SELECT  DISTINCT a. * 
FROM atlas_tl_students AS a
WHERE a.UID NOT 
IN ( 
SELECT b.Student_ID
FROM atlas_tl_session_grade_import AS b
WHERE b.Student_ID = a.UID AND a.Class_Year = b.Course_Year )

Pulls 315 records. It pulls all students from all other years. 2nd year thru 4th year students.

I only have 2 students who missed the exam but can't figure out the correct query to do this. What I eventually what to do is capture those students from the proper Class_Year who may have missed the exam and add them to the table and assign them a zero for this Session_ID.

[Sxooter]

There are two standard ways to find things that are in one table but not in another, either a subselect or a left join / null. Example:

select id from tablea where id not in (select id from tableb);

OR

select tablea.id from tablea left join tableb on (tablea.id=tableb.id) where tableb.id IS NULL;

While the first query is more obvious in how it works, the second one is generally faster in MySQL. Note that the second query is slower than the first in some databases, like Oracle 9i. Think about how it works. With a left join, every row from the left table is in the result set, but if it doesn't match any record in the right table, then the fields for the right table are NULL. So, every record in tablea that does NOT match tableb will be returned by this query, because of our null test.