Find at least one record from two where conditions from a join table-CodePudding

Hi I have a course table

course_id  name          type
1          Arthematic    Maths
2          Geometry      Maths
3          Chemistry     Science
4          Biological    Science
5          Hisotry       Social
6          Independence  Social

more records like this And student table

student_id    name          
    101       David
    102       Tony
    103       Skye
    104       Nicole
    105       Ossof

And a student_course many to many join table

id student_id course_id
1  102         2
2  102         3 
3  101         1
4  101         2
5  101         3
6  103         2
7  103         5
8  104         3
9  105         1
10 104         5

I am trying to find all the students who have both Maths and Science course types for a given course IDs. Means I need to find students who have atleast one Maths type course and atleast one Science course type for given input of course ids I have written this query but this gives student ids for any student who either Maths or Science or both

select sc.student_id from student_course sc left join course c on sc.course_id = c.id
where c.type = 'Maths' OR c.type = 'Science' and c.id IN (1,3,5)
group by  sc.student_id having count(sc.student_id) >= 2

CodePudding user response：

In this case, it would be better to use in operator to avoid logical mistakes related to parentheses, also you should use count with distinct to avoid counting the same types.

select sc.student_id
from student_course sc inner Join course c on sc.course_id = c.id
where c.type In ('Maths','Science') and c.id in (1,3,5)
group by sc.student_id 
having Count(distinct c.type) = 2

db<>fiddler

Result:

student_id
101