MySQL – query that should filter based on records contained in the join table

studentTable:

id
studentName

1
Name1

2
Name2

3
Name3

studentCourseTable:

id
studentId
courseId

1
1
1

2
1
2

3
3
1

4
3
3

5
2
2

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:

or:

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.


View on Stack Overflow