studentTable:

studentCourseTable:

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)

or:

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.

CodePudding user response：

You can select all information from your students and use two kind of JOIN operations:

• an INNER JOIN for each due attended course
• a LEFT JOIN for non-attended courses, to be filtered out in the WHERE clause

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.

CodePudding user response：

To get only students that are in groups 1 and 3

SELECT s.studentName
from studenttable AS s
INNER JOIN studentCourseTable AS sc ON sc.studentId = s.id
where sc.courseId in (1,3)
group by s.id
having count(*) = 2;

We first should group by student name or id, then we get only those that are in the 2 courses by adding "having count(*) = 2"