iSAMS Courses to Moodle View

SELECT DISTINCT
    TblSubjects.txtSubjectName,
    TblSets.txtSetCode AS txtCourseID,
    (TblSubjects.txtSubjectName + ' - ' + TblSets.txtSetCode) AS txtFullName,
    (TblSubjects.txtSubjectName + ' - ' + TblSets.txtSetCode) AS txtShortName,

    CASE TblSubjects.txtSubjectName
        WHEN 'Academic Extension' THEN 6
        WHEN 'Art' THEN 16
        WHEN 'Arabic' THEN 29
        WHEN 'Art Photography' THEN 22
        WHEN 'Art Textiles' THEN 17
        WHEN 'Biology' THEN 11
        WHEN 'Business Studies' THEN 15
        WHEN 'Chemistry' THEN 12
        WHEN 'Chinese' THEN 30
        WHEN 'Classics' THEN 34
        WHEN 'Drama' THEN 18
        WHEN 'Economics' THEN 15
        WHEN 'English' THEN 27
        WHEN 'English as an Additional Language' THEN 28
        WHEN 'English Language' THEN 27
        WHEN 'English Literature' THEN 27
        WHEN 'Film Studies' THEN 19
        WHEN 'Fine Art' THEN 16
        WHEN 'Food Technology' THEN 21
        WHEN 'French' THEN 29
        WHEN 'Further Mathematics' THEN 23
        WHEN 'Geography' THEN 25
        WHEN 'German' THEN 29
        WHEN 'Greek' THEN 31
        WHEN 'Gym' THEN 33
        WHEN 'Health Education' THEN 33
        WHEN 'History' THEN 26
        WHEN 'History of Art' THEN 20
        WHEN 'Hockey' THEN 33
        WHEN 'Home Economics' THEN 21
        WHEN 'Lacrosse' THEN 33
        WHEN 'Latin' THEN 31
        WHEN 'Learning Support' THEN 37
        WHEN 'Mandarin' THEN 30
        WHEN 'Mathematics' THEN 23
        WHEN 'Music' THEN 32
        WHEN 'Netball' THEN 33
        WHEN 'Philosophy & Ethics' THEN 36
        WHEN 'Physical Education' THEN 33
        WHEN 'Physics' THEN 13
        WHEN 'Politics' THEN 35
        WHEN 'Religious Studies' THEN 36
        WHEN 'Science' THEN 10
        WHEN 'Spanish' THEN 29
        WHEN 'Sport' THEN 33
        WHEN 'Swimming' THEN 33
        WHEN 'Textiles Technology' THEN 17
        WHEN 'Theatre Studies' THEN 18
        ELSE 8
    END AS intCategoryID

FROM
    TblTeachingManagerSets AS TblSets
    INNER JOIN TblTeachingManagerSubjects AS TblSubjects ON TblSets.intSubject = TblSubjects.TblTeachingManagerSubjectsID

WHERE
    (TblSubjects.txtSubjectName <> 'Private Study')