By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table course (id Int, name VarChar(20), [type] VarChar(20))
Create Table student_course (id Int, student_id Int, course_id Int)
Insert Into course (id, name, [type])
Values (1, 'Arthematic', 'Maths'),
(2, 'Geometry', 'Maths'),
(3, 'Chemistry', 'Science'),
(4, 'Biological', 'Science'),
(5, 'Hisotry', 'Social'),
(6, 'Independence', 'Social')
6 rows affected
Insert Into student_course (id, student_id, course_id)
Values (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)
10 rows affected
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
| student_id |
|---|
| 101 |