I'm not sure if the OP's original question is to find schools that have
ONLY and ALL of the courses in the criteria (e.g. 'maths' and 'english')
or to find schools that offer EITHER of the courses in the criteria (e.g. 'maths' or 'english').
Assuming, for example, we want to find schools that offer BOTH 'maths' and 'english' ONLY:
-- create temp tables
drop table #school
drop table #courses
create table #school ([id] int not null,name varchar(50))
create table #courses ([id] int not null, SchoolId int, name varchar(20))
-- insert test data
insert into #school
select 1,'school1'
union
select 2,'school2'
union
select 3,'school3'
union
select 4,'school4'
union
select 5,'school5'
insert into #courses
select 1,1,'maths'
union
select 1,2,'maths'
union
select 1,3,'maths'
union
select 1,4,'maths'
union
select 2,1,'english'
union
select 2,2,'english'
union
select 2,3,'english'
union
select 2,4,'english'
union
select 3,1,'geography'
union
select 3,2,'geography'
union
select 3,5,'geography'
union
select 2,5,'english'
-- use CTE to get the schools that ONLY have math and english:
;with SchoolCourse
AS
(select c.*
from #school s
inner join #courses c on s.id = c.SchoolId
where c.SchoolId in
(select c.SchoolId
from #courses c
group by c.SchoolId
having count(c.SchoolId) = 2)
and c.name in ('maths','english'))
select *
from SchoolCourse
where SchoolId in
(select SchoolId
from SchoolCourse
group by SchoolId
having count(SchoolId) = 2)
order by SchoolId,id