What will be the query for the following query?

Question

SELECT s.subject_id
     , s.subject_name
     , t.class_id
     , t.section_id
     , c.teacher_id
  FROM school_timetable_content c
  JOIN s  
    ON c.subject_id = s.subject_id
  JOIN school_timetables t 
    ON t.timetable_id = c.timetable_id
 WHERE c.teacher_id = 184
   AND t.class_id = 24
   AND t.school_id = 28

From the above query, I get the following result shown below:-

again from the above result I want to get subjects which are associated with all the unique section_id 15, 16,26. i.e Expected output Hindi,Maths

Answer 1

The idea is to filter for exactly those three sections. Then aggregate and count if all three are present:

SELECT s.subject_id, s.subject_name
FROM school_timetable_content tc JOIN
     school_subjects s
     ON tc.subject_id = s.subject_id JOIN
     school_timetables t
     ON t.timetable_id = tc.timetable_id
WHERE tc.teacher_id = 184 AND 
      t.class_id = 24 AND
      t.school_id = 28 AND
      t.section_id IN (15, 16, 26)
GROUP BY s.subject_id, s.subject_name
HAVING COUNT(*) = 3;

This assumes that section_id is not duplicated. If that is possible, use HAVING(COUNT(DISTINCT section_id)) = 3 instead.

Note that the use of table aliases makes the query easier to write and to read.