List the desired columns:


SELECT STU.FN, STU.LN, STU.ID, STU.SC, STU.GR,
  • Use a case statement to account for blank records
  • Concatenate 2 fields to create teacher name 
    CASE 
        WHEN STF.ID IS NULL THEN '' 
                     ELSE STF.FN + ' ' + STF.LN 
    END [PrimaryTeacher]


Join the necessary tables:


  • SEC - Student classes
FROM (SELECT * FROM SEC WHERE DEL = 0) SEC
  • Inner join to MST - Master Schedule
  • Limit to Master Schedule sections that have not been deleted and where Primary Class is true
    INNER JOIN (SELECT * FROM MST WHERE DEL = 0 AND MST.PC = 1) MST ON MST.SC = SEC.SC AND MST.SE = SEC.SE
  • Inner join to SSE - Section Staff
  • Limit to Section Staff records that have not been deleted and where Primary Teacher is true
INNER JOIN (SELECT * FROM SSE WHERE DEL = 0 AND SSE.PR = 1) SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE
  • Inner join STF to get teacher names
  • Limit to Staff records that have not been deleted 
    INNER JOIN (SELECT * FROM STF WHERE DEL = 0) STF ON SSE.ID = STF.ID
  • Right join STU to include students without a primary teacher
  • Limit to Student records that have not been deleted
    RIGHT JOIN (SELECT * FROM STU WHERE DEL = 0) STU ON SEC.SC = STU.SC AND SEC.SN = STU.SN


Define the constraints in the WHERE Clause:


  • Include only active students
WHERE STU.TG = ''
  • Include only flex schools
AND STU.SC IN (SELECT CD FROM LOC WHERE E = 3)
  • Include only schools using Primary Class Tracking
AND STU.SC IN (SELECT SC FROM OPT WHERE NM = 'TrackingPrimaryClasses' AND CD = 'True')



Finished query:


SELECT STU.FN, STU.LN, STU.ID, STU.SC, STU.GR, 
    CASE 
        WHEN STF.ID IS NULL THEN '' 
                            ELSE STF.FN + ' ' + STF.LN 
    END [PrimaryTeacher]
FROM (SELECT * FROM SEC WHERE DEL = 0) SEC
    INNER JOIN (SELECT * FROM MST WHERE DEL = 0 AND MST.PC = 1) MST ON MST.SC = SEC.SC AND MST.SE = SEC.SE 
    INNER JOIN (SELECT * FROM SSE WHERE DEL = 0 AND SSE.PR = 1) SSE ON MST.SC = SSE.SC AND MST.SE = SSE.SE
    INNER JOIN (SELECT * FROM STF WHERE DEL = 0) STF ON SSE.ID = STF.ID
    RIGHT JOIN (SELECT * FROM STU WHERE DEL = 0) STU ON SEC.SC = STU.SC AND SEC.SN = STU.SN 
WHERE STU.TG = ''
AND STU.SC IN (SELECT CD FROM LOC WHERE E = 3)
AND STU.SC IN (SELECT SC FROM OPT WHERE NM = 'TrackingPrimaryClasses' AND CD = 'True')