SQLTeam.com | Weblogs | Forums

Can multiple criteria be combined without nesting SELECT?

Hello

In a table like:

Col1 Col2 Col3
A 2000-01-01 TRUE
A 2000-01-02 FALSE
A 2000-01-03 TRUE
A 2000-01-04 FALSE
A 2000-01-05 TRUE

...

I want to return DISTINCT Col1 if (Col2=05/01/2000 and Col3=TRUE) but among these, if there is a (Col2<'2000-01-03' and Col3=TRUE) then it should be removed.

I know the easiest thing is to do

SELECT DISTINCT Col1
from TABLE
where  (Col2='2000-01-05' and Col3=TRUE)
and Col1 not in
(SELECT DISTINCT Col1
from TABLE
where  (Col2<'2000-01-03' and Col3=TRUE)

However, this doubles the processing effort as it does two separate filtering.
Is it possible to combine these in one SELECT/WHERE?

Thanks!


SELECT Col1
FROM TABLE
WHERE (Col2 = '2001-01-05' OR Col2 < '2001-01-03')
GROUP BY Col1
HAVING MAX(CASE WHEN Col2='2000-01-05' and Col3=TRUE THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Col2<'2000-01-03' and Col3=TRUE THEN 1 ELSE 0 END) = 0
1 Like