Applying multiple criteria in a SELECT statement
-
I am trying to apply a criteria on a select query where I want to select a school only if it offers 2 different courses like
maths and english
so
select s.Id,s.Name, c.* from courses c
INNER JOIN school s
ON c.SchoolId = s.Id
where c.Name = 'maths' or c.Name = 'english'
gets me all the schools that offer either of the 2 courses and then I have to filter the schools that offers both of the courses out from the collection of rows returned.
Is there a way I can write a select query that returns only those school which contains maths and english courses ?
Thanks in advance 🙂
-
-- Query 1SELECT s.Id, s.Name, c.*
FROM school s
INNER JOIN courses c
ON c.SchoolId = s.Id
WHERE EXISTS (SELECT 1 FROM courses WHERE SchoolId = s.Id AND [Name] = 'maths')
AND EXISTS (SELECT 1 FROM courses WHERE SchoolId = s.Id AND [Name] = 'english')
-- Query 2
SELECT s.Id, s.Name, c.*
FROM school s
INNER JOIN courses c
ON c.SchoolId = s.Id
INNER JOIN (SELECT SchoolId
FROM courses
WHERE [Name] IN ('maths', 'english')
GROUP BY SchoolId, [Name]
HAVING COUNT(*) > 1) f
ON f.SchoolId = s.Id
The first query is fairly obvious.
The second query looks for two rows per school in courses, where the course name is either english or maths - this query may not work, depends on the structure of the courses table.
Try both, I'd guess that the second query is more performant.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Untested, but try this
select s.Id,s.Namefrom courses c
INNER JOIN school s ON c.SchoolId = s.Id
where c.Name in ('maths' ,'english')
group by s.Id,s.Name
having count(distinct c.Name) >= 2
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Just another option from a logical standpoint (ignoring performance considerations which I doubt to play a role for this type of query, unless it's a monstrous school federation with millions of courses ;-)).
SELECT s.id
,s.name
,c.*
FROM school s
-- b subquery to filter schools which have courses in both maths & english
JOIN (SELECT SchoolId FROM courses WHERE name = 'maths'
INTERSECT
SELECT SchoolId FROM courses WHERE name = 'english') b ON s.id = b.SchoolId
JOIN courses c ON b.SchoolId = c.SchoolId
Best Regards,
Chris Büttner
-
Just for fun...
select s.Id,s.Namefrom courses c
INNER JOIN school s ON c.SchoolId = s.Id
where c.Name = 'maths'
intersect
select s.Id,s.Name
from courses c
INNER JOIN school s ON c.SchoolId = s.Id
where c.Name = 'english'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
If Performance is not the choice then i would like to go with INTERSECT.
This is pretty straught forward and can easily extendable.
Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers -
For fun? Here is no place for fun!!
... but there should also be a CTE solution:
WITH
schools (id, name) AS
(
SELECT 1, 'school 1'
UNION SELECT 2, 'school 2'
),
courses (id, school_id, name) AS
(
SELECT 1, 1, 'maths'
UNION SELECT 2, 1, 'english'
UNION SELECT 3, 2, 'maths'
UNION SELECT 4, 3, 'english'
),
my_courses (name) AS
(
SELECT 'maths'
UNION SELECT 'english'
)
SELECT s.id, s.name
FROM schools s
JOIN courses c ON s.id = c.school_id
JOIN my_courses mc ON c.name = mc.name
GROUP BY s.id, s.name
HAVING COUNT(*) > 1
:hehe:
-
OK, I've been enlightened here. I didn't even know about Intersect, so I immediately went to BOL to read about it and Except. I can definitely use these! :w00t:
Is the performance better than with Exists or Not Exists?
-
To say which one is better we need to test with million data.
but what is the volume of the data that you have.!
Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers -
I honestly don't know the size of the data. I write stored procedures and do reports, but no DBA stuff. I'd say our volume is low because it's a new application, but it has the potential to get large over time.
-
So for time being you can live..but you need to keep this in mind.
On the other note are you thinking about partitoins. You can easily modify the query but it takes time to redesign the table.
Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers -
Vijaya Kadiyala (4/2/2009)
...are you thinking about partitoins.Partitioning is "DBA stuff" so that is outside his scope.
Here is another solution. INTERSECT is needed when merging data from queries executed against different tables, but it is not necessary in this case.
create table Schools(SchoolID int Identity( 1, 1 ) not null Primary Key,
Name varchar( 50 ) not null,
);
go
create table Courses(
CourseID int Identity( 1, 1 ) not null Primary Key,
Name varchar( 50 ) not null,
SchoolID int,
);
go
alter table Courses
add contstraint FK_Courses_Schools
Foreign Key( SchoolID )
references Schools( SchoolID );
go
insert dbo.Schools
(name)
select 'East' union all
select 'West' union all
select 'North' union all
select 'South';
insert dbo.Courses
(Name, SchoolID)
select 'Physics', 1 union all
select 'Physics', 2 union all
select 'Physics', 3 union all
select 'Physics', 4 union all
select 'Math', 1 union all
select 'Math', 2 union all
select 'Math', 3 union all
select 'Math', 4 union all
select 'English', 2 union all
select 'English', 4;
-- They all teach physics and math but only West and South teach English
select s.SchoolID, s.Name
from dbo.Schools s
join dbo.Courses c
on s.SchoolID = c.SchoolID
where c.Name in( 'Math', 'English' )
group by s.SchoolID, s.Name
having count(*) = 2;
Plus you don't have to worry about it scaling because there is nothing in it to prevent it from scaling well.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Hi Tomm
You wrote "merged"... I know this feature is SQL Server 2008; for the sake of completeness.
Hope you allow to palm your sample data!
DECLARE @Result TABLE (Id INT, Name VARCHAR(50), Fits BIT)
INSERT INTO @Result (Id, Name)
SELECT s.SchoolID, s.Name
FROM Schools s
JOIN Courses c ON s.SchoolID = c.SchoolID
WHERE c.Name = 'Math'
MERGE @Result AS target
USING
(
SELECT s.SchoolId, s.Name
FROM Schools s
JOIN Courses c ON s.SchoolId = c.SchoolId
WHERE c.Name = 'English'
) AS source (Id, Name)
ON (target.Id = source.Id)
WHEN MATCHED
THEN UPDATE SET target.Fits = 1
;
SELECT *
FROM @Result r
WHERE Fits = 1
Greets
Flo
-
Florian Reischl (4/3/2009)
Hi TommHope you allow to palm your sample data!
That's what it's there for. I just wish more OPs would include it so we could all be working with the same tables loaded with the same data.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Tomm Carr (4/3/2009)
Here is another solution.
Your solution is pretty much identical to the one I posted a few days ago.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply