Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Posts
    32

    Unanswered: Finding missing records

    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.

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Code:
    EX:
    SELECT student, COUNT(exam)
    FROM...
    WHERE COUNT(exam)=0
    GROUP BY student
    SELECT * is not a good thing
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Posts
    32
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Posts
    32
    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.

  7. #7
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I just don't understand what table contains what data...

    Can you show us sample datas of every tables... ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  8. #8
    Join Date
    Nov 2002
    Posts
    32
    Here all the tables I am working with.

    Thanks for your help guys.

    `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);
    `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');
    `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');
    `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');

  9. #9
    Join Date
    Nov 2002
    Posts
    32
    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.

  10. #10
    Join Date
    Nov 2002
    Posts
    32
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •