2008/12/2 Igor Tandetnik <[EMAIL PROTECTED]>: > 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);
This would have a worse worst case scenario, but by cleverly ordering the inclusive statements from infrequent to frequent and the exclusive ones from frequent to infrequent this could be improved. I'd have to do a select m, count(m) from map group by m; to get the info I need for that. > 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. There's two tables with the same problem. One has an undetermined number of values: 'm' points to user-definable tag. In the other table I have about 110 values. This could be spread over two integer columns. I'm a bit hesitant to use integer values as bitmasks. How is the signedness handled in the binding? Should I simply use a uint64_t and not worry? Cheers, Jos _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
