SSP External Data Validation Queries Enhanced
Below are sample queries to validate external data loaded into SSP. All queries produce row counts in order given in the file SSPValidationQueriesEnhanced.doc. The majority of queries attempt to identify common problems such as:
- Orphaned rows (rows that exist in the external data that can't be tied to a row in external_person)
- Values in tables that are not configured in SSP
- Duplicate rows
SSPValidationQueriesEnhanced.doc | SSPValidationQueriesEnhanced.docx | SSPValidationQueriesEnhanced.odt | SSPValidationQueriesEnhanced.xls
A template that can be used: (the queries are below this template table in easy copy form) Note: to paste this into Google Drive Document, first paste into Google Sheet and then paste into Document
Table | Query | Description | Results |
---|---|---|---|
person | SELECT COUNT(*) FROM external_person | Row count | |
person | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null | Student row count | |
person | SELECT COUNT(DISTINCT race_code) FROM external_person WHERE race_code NOT IN (SELECT code FROM race) | Find race codes in external person not defined in race | |
person | SELECT COUNT(DISTINCT student_type_code) FROM external_person WHERE student_type_code NOT IN (SELECT code FROM student_type) | Find student type codes in external person not defined in student_type | |
person | SELECT COUNT(DISTINCT marital_status) FROM external_person WHERE marital_status NOT IN (SELECT name FROM marital_status) | Find marital status names in external person not defined in marital_status | |
person | SELECT COUNT(DISTINCT ethnicity) FROM external_person WHERE ethnicity NOT IN (SELECT name FROM ethnicity) | Find ethnicity names in external person not defined in ethnicity | |
person | SELECT COUNT(DISTINCT actual_start_term) FROM external_person WHERE actual_start_term NOT IN (SELECT code FROM external_term) | Find term_codes not defined in external_term | |
academic_program | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_academic_program) | Number of students with a row in academic program | |
academic_program | SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program | Distinct number of students with an academic program record | |
academic_program | SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Students defined in academic_program but not person | |
financial_aid | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_financial_aid) | Number of students with a row in financial_aid | |
financial_aid | SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid | Distinct number of students with a financial_aid record | |
financial_aid | SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Students defined in financial_aid but not person | |
test | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_test) | Number of students with a row in test | |
test | SELECT COUNT(DISTINCT school_id) FROM external_student_test | Distinct number of students with a test record | |
test | SELECT COUNT(DISTINCT school_id) FROM external_student_test WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Students defined in test but not person | |
transcript | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript) | Number of students with a row in transcript | |
transcript | SELECT COUNT(DISTINCT school_id) FROM external_student_transcript | Distinct number of students with an transcript record | |
transcript | SELECT COUNT(DISTINCT school_id) FROM external_student_transcript WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Students defined in transcript but not person | |
transcript_course | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_course) | Number of students with a row in transcript_course | |
transcript_course | SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Distinct number of students with a transcript_course record | |
transcript_course | SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id NOT IN (SELECT school_id FROM external_person) | Students defined in transcript_course but not person | |
transcript_course | SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course | Distinct rows for faculty ids | |
transcript_course | SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person) | Faculty ids that don’t exist in external person | |
transcript_course | SELECT COUNT(DISTINCT status_code) FROM external_student_transcript_course WHERE status_code NOT IN (SELECT code FROM enrollment_status) | Find status_codes not defined in configuration | |
transcript_course | SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_course WHERE term_code NOT IN (SELECT code FROM external_term) | Find term_codes not defined in external_term | |
transcript_course | SELECT COUNT(*) FROM external_student_transcript_course WHERE grade IS null | Find the number of courses without a grade | |
transcript_term | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_term) | Number of students with a row in transcript_term | |
transcript_term | SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term | Distinct number of students with a transcript_term record | |
transcript_term | SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Students defined in transcript_term but not person | |
transcript_term | SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_term WHERE term_code NOT IN (SELECT code FROM external_term) | Find term_codes not defined in external_term | |
registration_status_by_term | SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_registration_status_by_term) | Number of students with a row in registration_status_by_term | |
registration_status_by_term | SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term | Distinct number of students with a registration_status_by_term record | |
registration_status_by_term | SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) | Students defined in registration_status_by_term but not person | |
registration_status_by_term | SELECT COUNT(DISTINCT term_code) FROM external_registration_status_by_term WHERE term_code NOT IN (SELECT code FROM external_term) | Find term_codes not defined in external_term | |
faculty_course | SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course) | Number of faculty members with a row in faculty_course | |
faculty_course | SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course | Distinct number of faculty members with a faculty_course record | |
faculty_course | SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person) | Faculty defined in faculty_course but not person | |
faculty_course | SELECT COUNT(DISTINCT term_code) FROM external_faculty_course WHERE term_code NOT IN (SELECT code FROM external_term) | Find term codes not defined in external_term | |
faculty_course_roster | SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course_roster) | Number of faculty members with a row in faculty_course_roster | |
faculty_course_roster | SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster | Distinct number of faculty members with a faculty_course_roster record | |
faculty_course_roster | SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person) | Faculty defined in faculty_course_roster but not external_person | |
faculty_course_roster | SELECT COUNT(DISTINCT status_code) FROM external_faculty_course_roster WHERE status_code NOT IN (SELECT code FROM enrollment_status) | Find status code not defined in configuration | |
faculty_course_roster | SELECT COUNT(DISTINCT term_code) FROM external_faculty_course_roster WHERE term_code NOT IN (SELECT code FROM external_term) | Find term codes not defined in external_term | |
course | SELECT COUNT(*) FROM external_course | Count of courses | |
course | SELECT COUNT(DISTINCT formatted_course) FROM external_course | Count of distinct formatted courses | |
course | SELECT COUNT(DISTINCT division_code) FROM external_course WHERE division_code NOT IN (SELECT code FROM external_division) | Division codes not defined in external_division | |
course | SELECT COUNT(DISTINCT department_code) FROM external_course WHERE department_code NOT IN (SELECT code FROM external_department) | Department codes not defined in external_department | |
course | SELECT COUNT(DISTINCT code) FROM external_course WHERE code NOT IN (SELECT DISTINCT course_code FROM external_course_term) | Find courses that don’t have a term offering defined | |
course | SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite) | Find courses with requisite definitions | |
course | SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT course_code FROM external_course_program) | Find courses with course_program definitions | |
course_term | SELECT COUNT(DISTINCT term_code) FROM external_course_term WHERE term_code NOT IN (SELECT code FROM external_term) | Find term codes not defined in external_term | |
course_term | SELECT COUNT(DISTINCT course_code) FROM external_course_term WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course) | Find course_codes that don’t exist in external_course | |
course_program | SELECT COUNT(DISTINCT course_code) FROM external_course_program WHERE course_code NOT IN (SELECT code FROM external_course) | Find course_codes that don’t exist in external_course | |
course_program | SELECT COUNT(DISTINCT program_code) FROM external_course_program WHERE program_code NOT IN (SELECT DISTINCT code FROM external_program) | Find program_codes that don’t exist in external_program | |
course_requisite | SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite) | Find courses with course_requisite definitions | |
course_tag | SELECT COUNT(DISTINCT course_code) FROM external_course_tag WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course) | Find course_codes that don’t exist in external_course | |
course_tag | SELECT COUNT( distinct tag) FROM external_course_tag WHERE tag NOT IN (SELECT code FROM tag)
| Find tag codes that are not defined in tag | |
*** | Non Count based Queries | (must be run by manually) | Results |
academic_program
| SELECT school_id, COUNT(*) FROM external_student_academic_program GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Students in academic program with more than one academic_program record | |
financial_aid | SELECT school_id, COUNT(*) FROM external_student_financial_aid GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Students in academic program with more than one financial_aid record | |
transcript | SELECT school_id, COUNT(*) FROM external_student_transcript GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Students in external_student_transcript with more than one transcript record | |
transcript_term | SELECT school_id, term_code, COUNT(*) FROM external_student_transcript_term GROUP BY school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Students in transcript term with more than one transcript_term record | |
transcript_term | SELECT term_code, COUNT (distinct school_id) FROM external_student_transcript_term GROUP BY term_code ORDER BY term_code; | Distinct number of students by term with a transcript_term record | |
faculty_course | SELECT faculty_school_id, term_code, COUNT(*) FROM external_faculty_Course GROUP BY faculty_school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Faculty members in faculty_course with more than one faculty_course record | |
faculty_course | SELECT faculty_school_id, term_code, formatted_course, section_code, COUNT(*) FROM external_faculty_Course GROUP BY faculty_school_id, term_code, formatted_course, section_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Find duplicate rows in external_faculty_course | |
faculty_course | SELECT a.term_code, a.faculty_school_id, a.formatted_course, a.section_code FROM external_faculty_course AS a LEFT OUTER JOIN external_faculty_course_roster AS b ON a.term_code = b.term_code AND a.faculty_school_id = b. faculty_school_id AND a.formatted_course = b.formatted_course AND a.section_code = b.section_code WHERE b. term_code IS null AND b.faculty_school_id IS null AND b. formatted_course IS null AND b.section_code IS null; | Find courses in faculty_course with no rows in faculty_course_roster | |
faculty_course_roster | SELECT faculty_school_id, term_code, COUNT(*) FROM external_faculty_course_roster GROUP BY faculty_school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); | Faculty members with more than one faculty_course_roster record | |
faculty_course_roster | SELECT distinct a.term_code, a.faculty_school_id, a.formatted_course, a.section_code FROM external_faculty_course_roster AS a LEFT OUTER JOIN external_faculty_course AS b ON a.term_code = b.term_code AND a.faculty_school_id = b. faculty_school_id AND a.formatted_course = b.formatted_course AND a.section_code = b.section_code WHERE b. term_code IS null AND b.faculty_school_id IS null AND b.formatted_course IS null AND b.section_code IS null; | Find courses in faculty_course_roster with no rows in faculty_course |
------------
The code block below is a compilation of the count queries in order from above:
SELECT COUNT(*) FROM external_person UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null UNION ALL SELECT COUNT(DISTINCT race_code) FROM external_person WHERE race_code NOT IN (SELECT code FROM race) UNION ALL SELECT COUNT(DISTINCT student_type_code) FROM external_person WHERE student_type_code NOT IN (SELECT code FROM student_type) UNION ALL SELECT COUNT(DISTINCT marital_status) FROM external_person WHERE marital_status NOT IN (SELECT name FROM marital_status) UNION ALL SELECT COUNT(DISTINCT ethnicity) FROM external_person WHERE ethnicity NOT IN (SELECT name FROM ethnicity) UNION ALL SELECT COUNT(DISTINCT actual_start_term) FROM external_person WHERE actual_start_term NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_academic_program) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_academic_program WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_financial_aid) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_financial_aid WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_test) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_test UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_test WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_transcript UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_transcript WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_course) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_course WHERE school_id NOT IN (SELECT school_id FROM external_person) UNION ALL SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course UNION ALL SELECT COUNT(DISTINCT faculty_school_id) FROM external_student_transcript_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person) UNION ALL SELECT COUNT(DISTINCT status_code) FROM external_student_transcript_course WHERE status_code NOT IN (SELECT code FROM enrollment_status) UNION ALL SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_course WHERE term_code NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(*) FROM external_student_transcript_course WHERE grade IS null UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_student_transcript_term) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_student_transcript_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(DISTINCT term_code) FROM external_student_transcript_term WHERE term_code NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE coach_school_id IS NOT null AND school_id IN (SELECT DISTINCT school_id FROM external_registration_status_by_term) UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term UNION ALL SELECT COUNT(DISTINCT school_id) FROM external_registration_status_by_term WHERE school_id NOT IN (SELECT school_id FROM external_person WHERE coach_school_id IS NOT null) UNION ALL SELECT COUNT(DISTINCT term_code) FROM external_registration_status_by_term WHERE term_code NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course) UNION ALL SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course UNION ALL SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person) UNION ALL SELECT COUNT(DISTINCT term_code) FROM external_faculty_course WHERE term_code NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(school_id) FROM external_person WHERE school_id IN (SELECT DISTINCT faculty_school_id FROM external_faculty_course_roster) UNION ALL SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster UNION ALL SELECT COUNT(DISTINCT faculty_school_id) FROM external_faculty_course_roster WHERE faculty_school_id NOT IN (SELECT school_id FROM external_person) UNION ALL SELECT COUNT(DISTINCT status_code) FROM external_faculty_course_roster WHERE status_code NOT IN (SELECT code FROM enrollment_status) UNION ALL SELECT COUNT(DISTINCT term_code) FROM external_faculty_course_roster WHERE term_code NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(*) FROM external_course UNION ALL SELECT COUNT(DISTINCT formatted_course) FROM external_course UNION ALL SELECT COUNT(DISTINCT division_code) FROM external_course WHERE division_code NOT IN (SELECT code FROM external_division) UNION ALL SELECT COUNT(DISTINCT department_code) FROM external_course WHERE department_code NOT IN (SELECT code FROM external_department) UNION ALL SELECT COUNT(DISTINCT code) FROM external_course WHERE code NOT IN (SELECT DISTINCT course_code FROM external_course_term) UNION ALL SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite) UNION ALL SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT course_code FROM external_course_program) UNION ALL SELECT COUNT(DISTINCT term_code) FROM external_course_term WHERE term_code NOT IN (SELECT code FROM external_term) UNION ALL SELECT COUNT(DISTINCT course_code) FROM external_course_term WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course) UNION ALL SELECT COUNT(DISTINCT course_code) FROM external_course_program WHERE course_code NOT IN (SELECT code FROM external_course) UNION ALL SELECT COUNT(DISTINCT program_code) FROM external_course_program WHERE program_code NOT IN (SELECT DISTINCT code FROM external_program) UNION ALL SELECT COUNT(DISTINCT code) FROM external_course WHERE code IN (SELECT DISTINCT requiring_course_code FROM external_course_requisite) UNION ALL SELECT COUNT(DISTINCT course_code) FROM external_course_tag WHERE course_code NOT IN (SELECT DISTINCT code FROM external_course) UNION ALL SELECT COUNT(DISTINCT tag) FROM external_course_tag WHERE tag NOT IN (SELECT code FROM tag);
The final code block below is all of the manual queries that still need to be run and analysed manually, organized in order from above:
SELECT school_id, COUNT(*) FROM external_student_academic_program GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT school_id, COUNT(*) FROM external_student_financial_aid GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT school_id, COUNT(*) FROM external_student_transcript GROUP BY school_id HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT school_id, term_code, COUNT(*) FROM external_student_transcript_term GROUP BY school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT term_code, COUNT (distinct school_id) FROM external_student_transcript_term GROUP BY term_code ORDER BY term_code; SELECT faculty_school_id, term_code, COUNT(*) FROM external_faculty_Course GROUP BY faculty_school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT faculty_school_id, term_code, formatted_course, section_code, COUNT(*) FROM external_faculty_Course GROUP BY faculty_school_id, term_code, formatted_course, section_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT a.term_code, a.faculty_school_id, a.formatted_course, a.section_code FROM external_faculty_course AS a LEFT OUTER JOIN external_faculty_course_roster AS b ON a.term_code = b.term_code AND a.faculty_school_id = b. faculty_school_id AND a.formatted_course = b.formatted_course AND a.section_code = b.section_code WHERE b. term_code is null AND b.faculty_school_id is null AND b. formatted_course is null AND b.section_code IS null; SELECT faculty_school_id, term_code, COUNT(*) FROM external_faculty_course_roster GROUP BY faculty_school_id, term_code HAVING (COUNT(*) > 1) ORDER BY COUNT(*); SELECT distinct a.term_code, a.faculty_school_id, a.formatted_course, a.section_code FROM external_faculty_course_roster AS a LEFT OUTER JOIN external_faculty_course AS b ON a.term_code = b.term_code AND a.faculty_school_id = b. faculty_school_id AND a.formatted_course = b.formatted_course AND a.section_code = b.section_code WHERE b. term_code IS null AND b.faculty_school_id IS null AND b.formatted_course IS null AND b.section_code IS null;