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 1

    SELECT 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 Shaw

    For 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.Name

    from 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.Name

    from 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.

  • 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.!

  • 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.

  • 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 Tomm

    Hope 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