Two condition must be true in SQL

Question

I have two tables.

1. Student
2. Student Interest

Table: Student

Id | Student Name
------------------
1  | John
2  | Alice

Table: Student Interest

Id | SId | Interest
------------------
1  | 1   | Mathematics
2  | 1   | Science
1  | 2   | Environment
2  | 2   | English
2  | 2   | Mathematics

This two table is connected with the foreign key in "student Interest" Table

Now I want the name of students who has interest in both "Mathematics" and "science"

I tried this

Select s.Name from Student s
Inner Join StudentInterest si
ON
s.Id = si.SId
Where si.Interest IN ('Mathematics' , 'Science')

But it shows both student because both the student have interest in 'Mathematics' The result should be only 1 student named "John"

Answer 1

If you group by the student you can select only those having both interests like this

Select s.Name 
from Student s
Inner Join StudentInterest si ON s.Id = si.SId
Where si.Interest IN ('Mathematics' , 'Science')
group by s.Name
having count(distinct si.Interest) = 2

or

Select s.Name 
from Student s
Inner Join StudentInterest si ON s.Id = si.SId
group by s.Name
having sum(case when si.Interest = 'Mathematics' then 1 else 0 end) > 0
   and sum(case when si.Interest = 'Science' then 1 else 0 end) > 0