April 4, 2009 at 9:15 pm
Some cool stuff so far...can't wait for someone to wirte an XML/XQuery version just for giggles!
:rolleyes:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 10:33 am
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
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply