iSAMS to Moodle View v0.2

SELECT
    dbo.TblPupilManagementPupils.txtForename,
    dbo.TblPupilManagementPupils.txtSurname,
    dbo.TblPupilManagementPupils.txtEmailAddress,
    dbo.TblTeachingManagerSubjects.txtSubjectName,
    dbo.TblTeachingManagerSets.txtSetCode,
    '5' AS intRoleID
FROM
    dbo.TblTeachingManagerSubjects
    INNER JOIN dbo.TblTeachingManagerSets ON dbo.TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = dbo.TblTeachingManagerSets.intSubject
    INNER JOIN dbo.TblTeachingManagerSetLists ON dbo.TblTeachingManagerSets.TblTeachingManagerSetsID = dbo.TblTeachingManagerSetLists.intSetID
    INNER JOIN dbo.TblPupilManagementPupils ON dbo.TblTeachingManagerSetLists.txtSchoolID = dbo.TblPupilManagementPupils.txtSchoolID
WHERE
    (dbo.TblPupilManagementPupils.intSystemStatus = 1) AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL)

UNION ALL

SELECT
    TblStaff.Firstname AS txtForename,
    TblStaff.Surname AS txtSurname,
    TblStaff.SchoolEmailAddress AS txtEmailAddress,
    TblTeachingManagerSubjects.txtSubjectName,
    TblTeachingManagerSets.txtSetCode,
    '3' AS intRoleID
FROM
    TblTeachingManagerSets
    INNER JOIN TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID
    LEFT OUTER JOIN TblStaff ON TblTeachingManagerSets.txtTeacher = TblStaff.User_Code

WHERE
    (dbo.TblTeachingManagerSets.txtTeacher IS NOT NULL)

UNION ALL

SELECT
    TblAssStaff.Firstname AS txtForename,
    TblAssStaff.Surname AS txtSurname,
    TblAssStaff.SchoolEmailAddress AS txtEmailAddress,
    TblTeachingManagerSubjects.txtSubjectName,
    TblTeachingManagerSets.txtSetCode,
    '3' AS intRoleID
FROM
    TblStaff AS TblAssStaff
    INNER JOIN TblTeachingManagerSetAssociatedTeachers ON TblAssStaff.User_Code = TblTeachingManagerSetAssociatedTeachers.txtTeacher
    INNER JOIN TblTeachingManagerSubjects
    INNER JOIN TblTeachingManagerSets ON TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = TblTeachingManagerSets.intSubject
    ON TblTeachingManagerSetAssociatedTeachers.intSetID = TblTeachingManagerSets.TblTeachingManagerSetsID

WHERE
     (dbo.TblTeachingManagerSetAssociatedTeachers.txtTeacher IS NOT NULL)