Finding missing records

sdonovan · #1 (**permalink**) 01-31-08, 11:11

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 an exam. I need to find any missing students who may have missed class the day of the exam. If they missed the exam then they would not have a record in the imported table.

atlas_tl_session_grade_import has Student_ID and the grade.
atlas_tl_students has UID that should match Student_ID

The imported table (grade_import) also has a Session_ID for that class.

The Course table has several Session_ID's and it also has a Course_Year to designate if it is a first year course or second year course.

The student table has a Class_Year to identify if a student is a first year or second year student.

I need to find only those students for the respective year they are in for what course the grade was imported for.

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 a grade from the imported table.

I deleted one record from the imported table

If I run this:

Code:

SELECT  * 
FROM atlas_tl_students a
LEFT  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  NOT  NULL

I pulled 100 records.

Now if I run this:

Code:

SELECT  * 
FROM atlas_tl_students a
LEFT  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

Should I not get the missing record? I don't know why I can't figure this out.

ortho · #2 (**permalink**) 01-31-08, 11:27

Code:

EX:
SELECT student, COUNT(exam)
FROM...
WHERE COUNT(exam)=0
GROUP BY student

SELECT * is not a good thing

r937 · #3 (**permalink**) 01-31-08, 12:04

no, ortho, not like that

sdonovan, if you LEFT JOIN to b, then you have to LEFT JOIN from b to c, and also from c to d

sdonovan · #4 (**permalink**) 01-31-08, 12:14

This still doesn't work.

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

If I take out b.Student_ID IS NULL

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 d.Course_Year = a.Class_Year;

Then I get 100 records.

I need to test for missing records. I know for a fact I have one record that I deleted myself from atlas_tl_session_grade_import.

r937 · #5 (**permalink**) 01-31-08, 12:32

move d.Course_Year = a.Class_Year into the ON clause for the LEFT JOIN to d

Code:

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

sdonovan · #6 (**permalink**) 01-31-08, 13:47

I get 318 records. I am only expecting 1.

I get all 2nd, 3rd, and 4th year students and some random ones that don't have any Class_Year assigned.

ortho · #7 (**permalink**) 01-31-08, 13:50

I just don't understand what table contains what data...

Can you show us sample datas of every tables... ?

sdonovan · #8 (**permalink**) 01-31-08, 14:03

Here all the tables I am working with.

Thanks for your help guys.

Quote:

`atlas_tl_session_grade_import` (
`ReportNumber` int(3) default NULL,
`Student_ID` varchar(9) default NULL,
`Session_ID` int(11) default '0',
`Name_Last` varchar(50) default NULL,
`Name_First` varchar(30) default NULL,
`Total_RS` tinyint(4) default NULL,
`Total_Percent` tinyint(3) default NULL,
`Percentile` tinyint(3) default NULL,
`Subtest_RS` tinyint(3) default NULL,
`Subtest_Percent` tinyint(3) default NULL
)

(1, 'U00077193', 45, '"ABOUSOUD', ' OMAR "', 10, 100, 89, 10, 100);
(1, 'U00390918', 45, '"ABRAHAM', ' HEIDI "', 6, 60, 7, 6, 60);
(1, 'U00385334', 45, '"ADAMSHARRISON', ' ANGELA "', 6, 60, 7, 6, 60);
(1, 'U00545516', 45, '"AGIN', ' ANNIE "', 4, 40, 1, 4, 40);

Quote:

`atlas_tl_students` (
`Class_Year` tinyint(4) default NULL,
`Cohort` year(4) default NULL,
`Email` varchar(30) default NULL,
`Graduation_Year` year(4) NOT NULL default '0000',
`Name_First` text NOT NULL,
`Name_Last` text NOT NULL,
`Name_Middle` text,
`SOMS_KEY` int(11) NOT NULL default '0',
`SPRIDEN_PIDM` varchar(25) NOT NULL default '',
`UID` varchar(9) default NULL,
`LDAP_USER` varchar(255) NOT NULL default '',
PRIMARY KEY (`SOMS_KEY`),
UNIQUE KEY `GOBTPAC_LDAP_USER` (`LDAP_USER`)
)

(4, '2008', '', '2008', 'Sameer', 'Ali', 'S.', 1, '25856', 'U00025856', 'W106SSA\r');
(4, '2008', '', '2008', 'Deborah', 'Amann', 'M.', 2, '25857', 'U00025857', 'W108DMA\r');
(4, '2008', '', '2008', 'Jill', 'Aston', 'C.', 3, '25815', 'U00025815', 'W112JCA\r');
(4, '2008', '', '2008', 'Nicole', 'Bair', 'S.', 4, '25816', 'U00025816', 'W104NSB\r');

Quote:

`atlas_tl_session` (
`Session_ID` int(11) NOT NULL auto_increment,
`Course_ID` varchar(25) NOT NULL default '',
`Director_ID` varchar(25) NOT NULL default '',
`Session_Name` varchar(50) NOT NULL default '',
`Session_Detail` varchar(50) default NULL,
`TLDate` date NOT NULL default '0000-00-00',
`status` enum('1','2') NOT NULL default '1',
`Calendar_Year` varchar(5) NOT NULL default '',
PRIMARY KEY (`Session_ID`)
)

(40, 'CR00000007', '', 'MBM TL-1', 'Sickle Cell Anemia', '2007-10-23', '1', '0709');
(41, 'CR00000007', '', 'MBM TL-2', 'Hyperuriciemia', '2007-10-30', '1', '0709');
(42, 'CR00000007', '', 'MBM TL-3', 'Xeroderma Pigmentosum', '2007-11-13', '1', '0709');

Quote:

`atlas_tl_courses` (
`Course_ID` varchar(25) NOT NULL default '0',
`Course_Name` varchar(255) NOT NULL default '',
`Course_Number` mediumint(6) NOT NULL default '0',
`Course_Year` set('1','2') NOT NULL default '1',
`Peer_Eval` set('Yes','No') NOT NULL default '',
`Department` varchar(255) NOT NULL default '',
`multipart` enum('yes','no') NOT NULL default 'no',
PRIMARY KEY (`Course_ID`)
)

('CR00000001', 'Human Structure', 510, '1', 'Yes', 'SMD', 'no');
('CR00000002', 'Intro. to Clinical Medicine (ICM) I', 512, '1', 'No', 'SMD', 'no');
('CR00000003', 'Human Development', 513, '1', 'Yes', 'SMD', 'no');
('CR00000005', 'Principles of Disease', 530, '1', 'Yes', 'SMD', 'no');

sdonovan · #9 (**permalink**) 02-05-08, 10:01

Code:

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 b.Student_ID IS NULL;

Looking at this code again. The left outer join retrieves records from both tables, retrieving all the records from the atlas_tl_students table and any records from the atlas_tl_session_grade_import table where the Student_ID = UID values match.

If there are no matching values in from the atlas_tl_session_grade_import table, the join still retrieves all the records from the atlas_tl_students table. Any columns from the import table that are unmatched are left NULL.

Thus I should be looking for WHERE b.Student_ID IS NULL

I still cannot get this working and it is killing me. I have spun my wheels for 4 days on this query.

grrrrrrr

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.

Any help on this would be a life saver.

sdonovan · #10 (**permalink**) 02-05-08, 14:11

Ok... on the import table I added the Course_Year for each record.

So if I then do the following:

Code:

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;

I should then be able to find all records where the Class_Year and Course_Year match but don't exist in the import table.

Still not getting results.

Internet Services

Web Development

Digital Marketing

Consumer Tech