"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> 2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>:
>> Try this:
>>
>> select n from map
>> group by n
>> having
>> count(case when m=3 then 1 else null end) != 0 and
>> count(case when m=5 then 1 else null end) != 0 and
>> count(case when m=7 then 1 else null end) = 0;
>>
>> Having an index on map(n) should speed it up.
>
> Thank you very much, Igor. I would have not thought of that.
>
> This is a nicely predictable single linear scan. Still not awfully
> fast, but it will have to do.
You could also try something more straightforward:
select distinct n from map m1 where
exists (select 1 from map m2 where m1.n=m2.n and m2.m=3) and
exists (select 1 from map m2 where m1.n=m2.n and m2.m=5) and
not exists (select 1 from map m2 where m1.n=m2.n and m2.m=7);
-- or
select distinct n from map where
n in (select n from map where m=3) and
n in (select n from map where m=5) and
n not in (select n from map where m=7);
If you need to run this kind of query often, and values of m are small
(preferably less than 64), you might want to store a map from n to a
bitmask where each bit corresponds to one value of m. Then the query
becomes simply
select n from map
where (n & 168) = 40;
This is going to be linear, but in the number of distinct values of n,
not in the number of all pairs.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
