List the desired columns:


SELECT STU.FN, STU.LN, STU.ID, STU.SC, STU.GR,
SQL
  • 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]
SQL


Join the necessary tables:


  • SEC - Student classes
FROM (SELECT * FROM SEC WHERE DEL = 0) SEC
SQL
  • 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
SQL
  • 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
SQL
  • 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
SQL
  • 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
SQL


Define the constraints in the WHERE Clause:


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



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')
SQL