If I have a three column table, will call it AvailClasses

SchoolName, Subject, NumOfClasses
---------------------------------------------------------------
Emil, Biology, 3
Emil, Chemistry, 1
Emil, Calculus, 4
Prestwood, Biology, 2
Prestwood, Chemistry, 4
Prestwood, Calculus, 2
Westhaven, Biology, 1
Westhaven, Chemistry, 2
Westhaven, Calculus, 4

Using this subset of data as an example, I'd like to write a sql query
to return the results of all schools that have at least 2 Bilology
classes and at at least 4 Calculus classes

The query I came up with looks like this... It doesn't work.

SELECT SchoolName FROM AvilClasses WHERE
(Subject = 'Biology' AND NumOfClasses > 1) AND (Subject = 'Calculus'
AND NumOfClasses > 3).

The query above results in an empty set...

This has already been answered in
microsoft.public.sqlserver.programming

Do not post same question in multiple forums

Madhivanan


JDeats wrote:
> If I have a three column table, will call it AvailClasses
>
> SchoolName, Subject, NumOfClasses
> ---------------------------------------------------------------
> Emil, Biology, 3
> Emil, Chemistry, 1
> Emil, Calculus, 4
> Prestwood, Biology, 2
> Prestwood, Chemistry, 4
> Prestwood, Calculus, 2
> Westhaven, Biology, 1
> Westhaven, Chemistry, 2
> Westhaven, Calculus, 4
>
> Using this subset of data as an example, I'd like to write a sql query
> to return the results of all schools that have at least 2 Bilology
> classes and at at least 4 Calculus classes
>
> The query I came up with looks like this... It doesn't work.
>
> SELECT SchoolName FROM AvilClasses WHERE
> (Subject = 'Biology' AND NumOfClasses > 1) AND (Subject = 'Calculus'
> AND NumOfClasses > 3).
>
> The query above results in an empty set...