[thelist] Won't return results for 0 matches

Adam Patrick apatrick at oracular.com
Tue Jul 18 13:39:40 CDT 2000


CDitty wrote:
> 
> Does anyone see anything wrong with this statement as to why it won't
> return those that didn't match?  It works for 1-whatever, but not for 0.  I
> have physically verified that there are a few entries that have 0 matches
> in them.
> 
> select count(pick) as cnt, ticket
> from archivepicks
> where pick IN (11, 24, 29, 41, 49, 52)
> group by ticket
> having count(pick) = 0";
> 
> Any help is appreciated.
> 

Since your condition eliminates null values of pick: If count(pick) is
0, then by definition, there are no records to be sent back.  IOW, count
adds 1 for each row with a not-null value of pick so if pick is not in
11, 24, 29, 41, 49, 52, you won't get any rows.

if you are using Oracle, you could get the results you want this way. 
(Other databases someone else could chime in on)

select count(decode(pick, 11, 1, 24, 1, 29, 1, 41, 1, 49, 1, 52, 1,
null)), ticket
from archivepicks
group by ticket
having count(decode(pick, 11, 1, 24, 1, 29, 1, 41, 1, 49, 1, 52, 1,
null) = 0

--
Get your free, private FarmerDance at http://farmer.oracular.com/




More information about the thelist mailing list