Using embeded AND's in WHERE clause?

Using embeded AND's in WHERE clause?

Post by JDeat » Sat, 03 Jun 2006 04:17:19


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

Using embeded AND's in WHERE clause?

Post by markc60 » Sat, 03 Jun 2006 04:35:17


SELECT SchoolName
FROM AvailClasses
WHERE (Subject = 'Biology' AND NumOfClasses > 1)
OR (Subject = 'Calculus' AND NumOfClasses > 3)
GROUP BY SchoolName
HAVING COUNT(DISTINCT Subject)>1

 
 
 

Using embeded AND's in WHERE clause?

Post by Anith Se » Sat, 03 Jun 2006 05:24:07

There are several ways you can write them. While the Mark's solution may be
highly performant here are a couple of alternatives:

--#1
SELECT DISTINCT c1.SchoolName
FROM Classes c1
WHERE EXISTS( SELECT * FROM Classes c2
WHERE c2.SchoolName = c1.SchoolName
AND c2.Subject = 'Biology'
AND c2.NumOfClasses >= 2 )
AND EXISTS( SELECT * FROM Classes c2
WHERE c2.SchoolName = c1.SchoolName
AND c2.Subject = 'Calculus'
AND c2.NumOfClasses >= 4 ) ;

--#2
SELECT c1.SchoolName
FROM ( SELECT SchoolName FROM Classes
WHERE Subject = 'Biology' AND NumOfClasses >= 2 ) c1,
( SELECT SchoolName FROM Classes
WHERE Subject = 'Calculus' AND NumOfClasses >= 4 ) c2
WHERE c1.SchoolName = c2.SchoolName ;

--#3
SELECT *
FROM Classes c1
WHERE c1.Subject = 'Biology' AND c1.NumOfClasses >= 2
AND EXISTS ( SELECT *
FROM Classes c2 WHERE c2.SchoolName = c1.SchoolName
AND c2.Subject = 'Calculus' AND c2.NumOfClasses >= 4 ) ;

--
Anith