Finding missing records

[Donovan]

I need to find all missing Student_ID's from atlas_tl_session_grade_import, that don't exist in the atlas_tl_students table.

 

Here is my latest attempt.

 

SELECT a.* 
  FROM atlas_tl_students a 
LEFT JOIN atlas_tl_session_grade_import b 
    ON b.Student_ID = a.UID
LEFT JOIN atlas_tl_session c 
    ON c.Session_ID = b.Session_ID
LEFT JOIN atlas_tl_courses d 
    ON d.Course_ID = c.Course_ID
   AND d.Course_Year = a.Class_Year 
WHERE a.UID IS NULL;

 

I join atlas_tl_session c ON b.Session_ID = c.Session_ID to find out what Session (exam) they may have missed.

 

Then match atlas_tl_courses d ON c.Course_ID = d.Course_ID to find what course this exam was given.

 

Then constrain on Course_Year = Class_Year to find only those students (1st year or 2nd year) for that course.

 

Here is my relevant table structure

 

atlas_tl_students

`Class_Year` tinyint(4) default NULL,

`UID` varchar(9) default NULL,

 

atlas_tl_session_grade_import

`Student_ID` varchar(9) default NULL,

`Session_ID` int(11) default '0',

 

atlas_tl_session

`Session_ID` int(11) NOT NULL auto_increment,

`Course_ID` varchar(25) NOT NULL default '

 

atlas_tl_courses

`Course_ID` varchar(25) NOT NULL default '0',

`Course_Year` set('1','2') NOT NULL default '1',

 

I've been working on this problem since Wednesday last week and can't seem to get it working.    In my example I need to find any first year students (Class_Year = 1) that may have missed an exam (Session_ID = 45). I match the Session_ID to the Course it was assigned to, then find the Course_Year and match that to the Class_Year to pull any first years medical students who's Student_ID 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.

 

[fenway]

Why so many left joins? You only need 2 tables and a lot of on conditions.

[Donovan]

Why so many left joins? You only need 2 tables and a lot of on conditions.

 

Could you please explain?

[Donovan]

How could I only use two tables?  I have approximately 415 students who are 1st year through 4th year students.  Class_Year = 1,2,3,4

 

I need to find only those students for that Session_ID which belongs to the correct course for the exam given.  So far the courses I have loaded are either a 1st year course or 2nd year course.  I match Course_Year of the course to Class_Year of student to find the correct students to query from.

[fenway]

Sorry, I misread the original post.  a.anything will never be null in a left join... you need to check the b. table.  And don't you want inner join for everything after the import table?

[Donovan]

I simplyfied this by adding the Course_Year for each record to the import table.

 

So now I am trying this:

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

 

This should get me all 1st year students, but so far it is not.

 

 

[Donovan]

Still trying to get this working.

 

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.

[fenway]

You can't check b.CourseYear and b.StudentID is NULL together... if the latter is null, it's because all b table columns were nulled out... you need to put this condition into the on clause.