I want to (let’s say) list students who have taken courseId 1 AND 3 (together) BUT have NOT taken 2. Or any dynamic combination such as that, like courseId 1,2,3 should be taken; 1,2 not taken but 3 is taken etc etc.
I have tried some JOIN clause to filter but have not been able to apply more than 1 condition:
SELECT student.* FROM studentTable AS s LEFT JOIN studentCourseTable AS sc ON sc.studentId = s.id WHERE sc.studentId IN (1,3) AND sc.studentId NOT IN (2)
SELECT student.* FROM studentTable AS s LEFT JOIN studentCourseTable AS sc ON sc.studentId = s.id AND sc.courseId IN (1, 3) AND sc.courseId IN (2)
The important thing is that I want to find students that take specified courses TOGETHER, AND not take any other specified course. The student may take more courses than specified (as long as it is not in NOT taken list).
**Edit for some clarifications: ** For example if I say the student should take (2,4) but NOT (3), returning a student that takes (2,4,5) is ok. But (2,3) or (2,4,5) are NOT ok.
There are some other tables that I’m joining the student table with, not sure if it matters but this is the gist of it.
Can anyone assist me with this?
** Edit: ** @lemon has cracked it. Here’s the demo he made, which lists any user that attended 1 OR 3 AND have not attended 2. Here’s my updated demo which lists students that attended 1 AND 3 AND have not attended 2.
Thanks to all who helped me, this was superb.
You can select all information from your students and use two kind of
INNER JOINfor each due attended course
LEFT JOINfor non-attended courses, to be filtered out in the
SELECT s.* FROM students s INNER JOIN (SELECT DISTINCT studentId FROM courses WHERE courseId = 1) c1 ON s.id = c1.studentId INNER JOIN (SELECT DISTINCT studentId FROM courses WHERE courseId = 3) c3 ON s.id = c3.studentId LEFT JOIN (SELECT DISTINCT studentId FROM courses WHERE courseId IN (2)) not_c ON s.id = not_c.studentId WHERE not_c.studentId IS NULL
Check the demo here.
Another option is to count
- positively your needed courses
- negatively your unneeded courses
Eventually sum up the values, and filter out those students whom don’t have sum equal to the amount of needed courses.
SELECT s.* FROM courses c INNER JOIN students s ON s.id = c.studentId GROUP BY s.id, s.studentName HAVING SUM(CASE WHEN c.courseId IN (1,3) THEN 1 WHEN c.courseId IN (2) THEN -1 END) = 2
Check the demo here.
Answered By – lemon