October 12, 2016 at 2:15 pm
My apologies for the first draft of my question. I have removed the excess code, provided data from the two tables, as well as an example of what is expected from the query. My ultimate goal is to generate a list of missing subject codes from the Responsibility table however I currently get a list of matching subject codes.
CREATE TABLE Responsibility ( [TeacherID] int, [SPN] varchar(16), [TeacherName] varchar(82), [SubjectCode] int )
INSERT INTO Responsibility
VALUES
( 28, '10621334', 'Jane Doe', 500 ),
( 28, '10621334', 'Jane Doe', 524 ),
( 28, '10621334', 'Jane Doe', 4562 ),
( 28, '10621334', 'Jane Doe', 5260 ),
( 28, '10621334', 'Jane Doe', 5268 ),
( 28, '10621334', 'Jane Doe', 5518 ),
( 28, '10621334', 'Jane Doe', 5902 ),
( 28, '10621334', 'Jane Doe', 5966 ),
( 28, '10621334', 'Jane Doe', 5974 );
CREATE TABLE CourseInfo ( [CourseStateCode] int, [SchoolCourseID] varchar(20), [CourseTeacherID] int )
INSERT INTO CourseInfo
VALUES
( 4562, '1870', 28 ),
( 5268, '1872', 28 ),
( 5394, '1745', 28 )
Desired results would look like
CourseStateCode, CourseTeacherID, SchoolCourseID,TeacherID,SPN, TeacherName, SubjectCode
5394 28 1745 28 10621334 Jane Doe NULL
Query as it exists now.
SELECT DISTINCT
CourseInfo.CourseStateCode ,
CourseInfo.CourseTeacherID ,
CourseInfo.SchoolCourseID ,
Responsibility.TeacherID ,
Responsibility.SPN ,
Responsibility.TeacherName ,
Responsibility.SubjectCode
FROM Left JOIN Responsibility ON CourseInfo.CourseTeacherID + CourseInfo.CourseStateCode =
Responsibility.TeacherID + Responsibility.SubjectCode
WHERE Responsibility.TeacherID = '28';
What I get is a list of codes that exist in both the CourseInfo and the Responsibility table.
CourseStateCode, CourseTeacherID, SchoolCourseID,TeacherID,SPN, TeacherName, SubjectCode
5268 28 1872 28 10621334 Jane Doe 5268
4562 28 1870 28 10621334 Jane Doe 4562
October 12, 2016 at 2:25 pm
mareynolds (10/12/2016)
Good afternoon. I need some assistance getting pointed in the right direction. My ultimate goal is to produce rows where there is not a matching courseinfo.coursestatecode in the responsibility.subjectcode for a teacher. Our HR staff will then use this list to generate the missing responsibility records. Currently, the SQL below generates a row for each responsibility.subjectcode a teacher has.
DECLARE @SchoolYear NCHAR(4);
SET @SchoolYear = '2017';
WITH Responsibility
AS ( SELECT DISTINCT staff.name_id AS 'TeacherID' ,
staff.in_sch_personnel_nbr AS 'SPN' ,
staff.long_name AS 'TeacherName' ,
/*StateRpt.date_fld_01 AS 'StartDate' ,
StateRpt.date_fld_02 AS 'EndDate' ,
StateRpt.int_fld_01 AS 'SPEDClassSetting' ,
StateRpt.char_fld_01 AS 'HighlyQualifiedTchr' ,*/
StateRpt.int_fld_02 AS 'SubjectCode'
FROM dbo.staff
INNER JOIN dbo.name ON staff.name_id = name.name_id
INNER JOIN dbo.ssrpt_state_reporting AS StateRpt ON staff.name_id = StateRpt.src_id
INNER JOIN dbo.staff_entity ON staff.name_id = staff_entity.name_id
WHERE staff_entity.status_cur_yr = 'A'
AND ( staff.x_teacher = '1'
OR staff.x_substitute = '1'
)
)
SELECT DISTINCT
CourseInfo.CourseStateCode ,
CourseInfo.CourseTeacherID ,
CourseInfo.SchoolCourseID ,
Responsibility.TeacherID ,
Responsibility.SPN ,
Responsibility.TeacherName ,
/*Responsibility.StartDate ,
Responsibility.EndDate,
Responsibility.SPEDClassSetting ,
Responsibility.HighlyQualifiedTchr ,*/
Responsibility.SubjectCode
FROM ( SELECT DISTINCT
course.mn_cor_assign_code AS 'CourseStateCode' ,
course.cor_alphakey AS 'SchoolCourseID' ,
class_meet.name_id AS 'CourseTeacherID'
FROM dbo.student_entity
INNER JOIN dbo.entity ON student_entity.entity_id = entity.entity_id
INNER JOIN dbo.student_class ON student_entity.student_id = student_class.student_id
AND student_entity.entity_id = student_class.entity_id
AND student_class.school_year = @SchoolYear
INNER JOIN dbo.class ON student_class.cor_num_id = class.cor_num_id
AND student_class.track = class.track
AND student_class.clas_section = class.clas_section
AND clas_status = 'A'
INNER JOIN dbo.clas_control_set ON class.entity_id = clas_control_set.entity_id
AND class.track = clas_control_set.track
AND class.control_set_id = clas_control_set.control_set_id
AND clas_control_set.school_year = @SchoolYear
AND control_set_ref = '0'
AND clas_control_set.school_year = @SchoolYear
AND dbo.clas_control_set.control_set_id IN (
'S1', 'YR', 'Q1' )
INNER JOIN dbo.class_meet ON class.cor_num_id = class_meet.cor_num_id
AND class.track = class_meet.track
AND class.clas_section = class_meet.clas_section
AND class_meet.school_year = @SchoolYear
INNER JOIN dbo.name AS Teacher ON class_meet.name_id = Teacher.name_id
INNER JOIN dbo.course ON class_meet.cor_num_id = course.cor_num_id
AND cor_status = 'A'
INNER JOIN dbo.staff ON Teacher.name_id = staff.name_id
) AS CourseInfo
right JOIN Responsibility ON --CourseInfo.CourseStateCode = Responsibility.SubjectCode
CourseInfo.CourseTeacherID = Responsibility.TeacherID
WHERE Responsibility.SPN = '10621334'
AND CourseInfo.CourseStateCode <> Responsibility.SubjectCode;
-- AND CourseInfo.CourseStateCode IS NULL;
CourseStateCodeCourseTeacherIDSchoolCourseIDTeacherIDSPNTeacherNameSubjectCode
02891052810621334Jane Doe500
45622818702810621334Jane Doe500
52682818722810621334Jane Doe500
53942817452810621334Jane Doe500
02891052810621334Jane Doe524
45622818702810621334Jane Doe524
52682818722810621334Jane Doe524
53942817452810621334Jane Doe524
02891052810621334Jane Doe4562
52682818722810621334Jane Doe4562
53942817452810621334Jane Doe4562
02891052810621334Jane Doe5260
45622818702810621334Jane Doe5260
52682818722810621334Jane Doe5260
53942817452810621334Jane Doe5260
02891052810621334Jane Doe5268
45622818702810621334Jane Doe5268
53942817452810621334Jane Doe5268
02891052810621334Jane Doe5518
45622818702810621334Jane Doe5518
52682818722810621334Jane Doe5518
53942817452810621334Jane Doe5518
02891052810621334Jane Doe5902
45622818702810621334Jane Doe5902
52682818722810621334Jane Doe5902
53942817452810621334Jane Doe5902
02891052810621334Jane Doe5966
45622818702810621334Jane Doe5966
52682818722810621334Jane Doe5966
53942817452810621334Jane Doe5966
02891052810621334Jane Doe5974
45622818702810621334Jane Doe5974
52682818722810621334Jane Doe5974
53942817452810621334Jane Doe5974
You need to use OUTER JOINS (left or right). I can't give you a coded answer because I'm not able to test it.
October 12, 2016 at 3:08 pm
Since English is a left-to-right language, you will almost exclusively see people using LEFT outer joins.
A RIGHT outer join says include all records from the RIGHT table (Responsibilities) regardless of whether there are matches on the left side (Course Info) whereas you want all records from the LEFT table regardless of whether there are matches from the RIGHT table, so you should be using a LEFT (not RIGHT) outer join. And English speakers prefer left outer joins anyhow, as previously stated.
Your where clause includes a condition on the missing data that converts the OUTER JOIN to an INNER JOIN. It's not clear how to correct this problem, because you are missing sample data and expected results.
-- This converts your outer join to an inner join
AND CourseInfo.CourseStateCode <> Responsibility.SubjectCode;
Drew
I realize that you included a table, but it's not clear which table that is and we need data for each of the tables included in the query. HOWEVER, you've given us both too much information and too little information. We don't really need to know EVERY SINGLE TABLE that goes into your query, we only need enough information to reconstruct the problem. For instance, your derived table is a table. We don't need to know how the table arrived at that state, we only need that table.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 14, 2016 at 8:33 am
Again, your WHERE clause is converting your OUTER JOIN to an INNER JOIN. You want to find record where the value is MISSING. Use the following.
SELECT DISTINCT
CourseInfo.CourseStateCode ,
CourseInfo.CourseTeacherID ,
CourseInfo.SchoolCourseID ,
Responsibility.TeacherID ,
Responsibility.SPN ,
Responsibility.TeacherName ,
Responsibility.SubjectCode
FROM CourseInfo
LEFT JOIN Responsibility ON CourseInfo.CourseTeacherID = Responsibility.TeacherID
AND CourseInfo.CourseStateCode = Responsibility.SubjectCode
WHERE Responsibility.TeacherID IS NULL;
I also changed your join criteria. You should avoid calculations in JOIN and WHERE criteria whenever possible, because it prevents the optimizer from using a seek. Also, this particular calculation will produce the same results for different pairs producing false matches.
Finally, some of the tables that you had in your original derived table need to be moved to the outer query, so that the teacher's name and the SPN can be included in your output. Since you are specifically looking for rows with missing information, and you have set it up so they are only appearing in the side that's missing, they will be missing in the results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply