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
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