Find records as And in MYSQL

Question

I want find Students id that , must have (professorId = 3 And courseId = 4) And (professorId = 3 And courseId = 2) And (professorId = 5 And courseId = 8). sometime these parenthesis is one or more than 20

I have two table class and studentsClasses like this :

studentsClasses table:

id | studentId | classId
1        1         2
2        1         3
3        1         4
4        2         3
5        2         5
6        3         2
7        4         3
8        4         2
9        1         8

Class Table:

id | professorId | CourseId

1        1            1
2        3            4
3        2            5
4        3            2
5        4            2
6        2            3
7        5            6
8        5            8
9        5            9

It would be very simple if every parenthesis worked as or. But I want to find a student who has all these things in the form of and.

This query is "OR" scenario:

select `studentsClasses`.`studentId` from `studentsClasses`
 inner join `class` on (`studentsClasses`.`classId`=`class`.`id` 
AND ((professorId = 3 And courseId = 4) OR (professorId = 3 And courseId = 2) 
   OR (professorId = 5 And courseId = 8)))

But i want AND scenario like this:

select `studentsClasses`.`studentId` from `studentsClasses`
     inner join `class` on (`studentsClasses`.`classId`=`class`.`id` 
    AND ((professorId = 3 And courseId = 4) AND (professorId = 3 And courseId = 2) 
       AND (professorId = 5 And courseId = 8)))

But above query is wrong.

For example result query with these value should be :

studentId
---------
1

Answer 1

One way is to use CASE WHEN condition and SUM over it.

SELECT
  studentId,
  SUM(
    CASE
      WHEN c.professorId = 3 AND c.courseId = 4 THEN 1
      WHEN c.professorId = 3 AND c.courseId = 2 THEN 1
      WHEN c.professorId = 5 AND c.courseId = 8 THEN 1
      ELSE 0
    END) AS filter_cnt
FROM studentsClasses s
JOIN class c ON c.id = s.classId
GROUP BY 1
HAVING filter_cnt = 3

Another way is to use a separate join for each condition. Not an efficient solution, but it might be fine if data is not too big.

WITH class_1 AS (
  SELECT DISTINCT studentId FROM studentsClasses s
  JOIN class c ON c.id = s.classId AND c.professorId = 3 AND c.courseId = 4
),

class_2 AS (
  SELECT DISTINCT studentId FROM studentsClasses s
  JOIN class c ON c.id = s.classId AND c.professorId = 3 AND c.courseId = 2
),

class_3 AS (
  SELECT DISTINCT studentId FROM studentsClasses s
  JOIN class c ON c.id = s.classId AND c.professorId = 5 AND c.courseId = 8
)

SELECT c1.studentId FROM class_1 c1
JOIN class_2 c2 ON c2.studentId = c1.studentId
JOIN class_3 c3 ON c3.studentId = c1.studentId