Finding missing records

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

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.

Code:

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.

Code:

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

Posted: Mon Feb 04, 2008 10:11 am

Still looking for help with this.

Code:




SELECT a.* 


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 a.Class_Year = d.Course_Year AND b.Student_ID IS  NULL

Here is my relevant table structure...

Quote:

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 need to find all missing Student_ID's from atlas_tl_session_grade_import, that don't exist in the atlas_tl_students table

UID = Student_ID

I first 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.

Any help is appreciated. If I do the joins table by table I get 315 records until the last WHERE clause

If I leave out the Student_ID IS NULL I get 100 records that exist in the import table, except for the missing 2 records from the import.

If I leave out the Course_Year = Class_Year I get all 315 student except for 100 1st years students who exist in the import table.

I've been working on this problem since Wednesday last week. Crying or Very sad

registered · Site Admin Joined: Jun 04, 2004 Posts: 6437

This gave me a headache... Smile

I'd suggest having a select that identifies students, courses, the do a subselect (a select within a select) that counts the number of records in the import table that match, displaying only those that have less than 1 record.

e.g.

select * from table 1, table 2... where 1.field = 2.field and 2.field = 3.field (etc. for all fields) and 1 > (select count(1) from import i where i.field = 1.field and i.field = 2.field)

Make sense?

Posted: Tue Feb 05, 2008 8:23 am

kguske wrote:

Make sense?

Unfortunately no.

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. UID and Student_ID and the same thing just named differently. 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.

registered · Posted: Tue Feb 05, 2008 9:17 pm

Donovan, I have to admit that my SQL experience is mostly from Oracle, so not sure if it applies in mySQL (and, no, I have not looked it up), but I wonder if you could use a WHERE NOT EXISTS. The inner select would have to join the two tables.

Posted: Wed Feb 06, 2008 8:00 am

What I did was try to simplify by adding the Course_Year to each record in the import table. So each record no has a value that will match the Class_Year for each student in the student table.

My first attempt was the following:

Code:

SELECT  a.* 


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

Pulling back 315 records meaning all students except for those that match from the import table. I should only be pulling in a couple where a.Class_Year = b.Course_Year since I only have 2 first year students that are missing from the imported table. The Course_year in the imported table is a value 1, and I have approximately 102 first year students, (Class_Year = 1) . Two students missed the exam and they do not have a record in the imported table. I want to pull just these two students instead I am getting all 315.

My second attempt:

Code:




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 )

Still not working.

Posted: Wed Feb 06, 2008 12:10 pm

I think I finally got this working.

Code:




SELECT DISTINCT a.* 


FROM ".$prefix."_tl_students AS a


JOIN ".$prefix."_tl_session_grade_import AS b


WHERE a.Class_Year = b.Course_Year AND a.UID NOT 


IN ( 


SELECT Student_ID


FROM ".$prefix."_tl_session_grade_import)