Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
744 B
2
Indexable
Never
	SELECT 
		tt.Fname AS [Teacher first name],
		tt.Lname AS [Teacher last name],
		CASE WHEN tt.Email IS NULL THEN 'Not set' ELSE tt.Email END AS "Teacher email",
		tc.class_name AS "Class name",
		COALESCE(CAST(tc.class_capacity AS VARCHAR(50)), 'Not set') AS "Class capacity",
		COUNT(tsc.studentNumber) AS "Number of students"
    FROM tblteachers tt
    JOIN  tblClass tc ON (
        tt.teacherID = tc.teacher1_id OR
        tt.teacherID = tc.teacher2_id OR
        tt.teacherID = tc.teacher3_id OR
        tt.teacherID = tc.teacher4_id OR
        tt.teacherID = tc.teacher5_id
		)
	JOIN tblStudentClass tsc ON tsc.ClassID=tc.ClassID
	GROUP BY 
		tt.Fname,
		tt.Lname,
		tt.Email,
		tc.class_name,
		tc.class_capacity