Hi all,
I have crafted the following query which returns the results I want but
runs extremely slow (I'm sure it's the "not in" part that does it) since
the tables can contain large amounts of data.
Is there a better, more efficient way to build this query?
It's important that I return zero counts for id flag combinations that do not
have an entry in the log table hence the union with the "not in":
id is a text field and flgtime is a timestamp. flags is a table that contains
a single column with a row for each unique flag (text).
select id, flag, count(*) as count from logwhere date(flgtime) = '2002-04-16' and flag is not null
group by id, flag
union
select distinct l.id, f.flag, 0 as count from log l, flags fwhere (l.id, f.flag) not in (select id, flag
fromlog where date(flgtime) = '2002-04-16' and fag is not null)
group by l.id, f.flag
;
Thanks for any suggestions.
--
Laurette Cisneros
Database Roadie
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Where's my....bus?
