"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> I've trouble optimizing for an N:M mapping table. The schema of the
> table is this:
>
> CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL);
>
> I want to retrieve a list of n filtered on the presence of certain
> values of m, e.g. give me all n for which there is an m = 3 and m = 5,
> but no m = 7.
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.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
