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

Issue

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:

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.

Solution

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.


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

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published