Advertisement
jonwitts

iSAMS to Moodle View v0.2

Jun 27th, 2014
404
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT      
  2.     dbo.TblPupilManagementPupils.txtForename,
  3.     dbo.TblPupilManagementPupils.txtSurname,
  4.     dbo.TblPupilManagementPupils.txtEmailAddress,
  5.     dbo.TblTeachingManagerSubjects.txtSubjectName,
  6.     dbo.TblTeachingManagerSets.txtSetCode,
  7.     '5' AS intRoleID
  8. FROM        
  9.     dbo.TblTeachingManagerSubjects
  10.     INNER JOIN dbo.TblTeachingManagerSets ON dbo.TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = dbo.TblTeachingManagerSets.intSubject
  11.     INNER JOIN dbo.TblTeachingManagerSetLists ON dbo.TblTeachingManagerSets.TblTeachingManagerSetsID = dbo.TblTeachingManagerSetLists.intSetID
  12.     INNER JOIN dbo.TblPupilManagementPupils ON dbo.TblTeachingManagerSetLists.txtSchoolID = dbo.TblPupilManagementPupils.txtSchoolID
  13. WHERE    
  14.     (dbo.TblPupilManagementPupils.intSystemStatus = 1) AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL)
  15.  
  16. UNION ALL
  17.  
  18. SELECT        
  19.     TblStaff.Firstname AS txtForename,
  20.     TblStaff.Surname AS txtSurname,
  21.     TblStaff.SchoolEmailAddress AS txtEmailAddress,
  22.     TblTeachingManagerSubjects.txtSubjectName,
  23.     TblTeachingManagerSets.txtSetCode,
  24.     '3' AS intRoleID
  25. FROM            
  26.     TblTeachingManagerSets
  27.     INNER JOIN TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID
  28.     LEFT OUTER JOIN TblStaff ON TblTeachingManagerSets.txtTeacher = TblStaff.User_Code
  29.  
  30. WHERE
  31.     (dbo.TblTeachingManagerSets.txtTeacher IS NOT NULL)
  32.    
  33. UNION ALL
  34.  
  35. SELECT        
  36.     TblAssStaff.Firstname AS txtForename,
  37.     TblAssStaff.Surname AS txtSurname,
  38.     TblAssStaff.SchoolEmailAddress AS txtEmailAddress,
  39.     TblTeachingManagerSubjects.txtSubjectName,
  40.     TblTeachingManagerSets.txtSetCode,
  41.     '3' AS intRoleID
  42. FROM            
  43.     TblStaff AS TblAssStaff
  44.     INNER JOIN TblTeachingManagerSetAssociatedTeachers ON TblAssStaff.User_Code = TblTeachingManagerSetAssociatedTeachers.txtTeacher
  45.     INNER JOIN TblTeachingManagerSubjects
  46.     INNER JOIN TblTeachingManagerSets ON TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = TblTeachingManagerSets.intSubject
  47.     ON TblTeachingManagerSetAssociatedTeachers.intSetID = TblTeachingManagerSets.TblTeachingManagerSetsID
  48.    
  49. WHERE
  50.      (dbo.TblTeachingManagerSetAssociatedTeachers.txtTeacher IS NOT NULL)
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement