> select * from foo f1 where > (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and > f2.b in (...) )) = > length_of_b_list > and b in (...);
Shouldn't this be simplified like this? select * from foo f1 where (select count(distinct b) from foo f2 where f1.a = f2.a and f2.b in (...) ) = length_of_b_list and b in (...); And here's another form of the same idea. It could be better because it doesn't force to execute inner query for each row of outer table - it can be executed the opposite way and thus become faster in certain situations. select f1.* from foo f1, (select f2.a a, count(distinct f2.b) cnt_b from foo f2 where f2.b in (...) group by f2.a) f3 where f1.a = f3.a and f3.cnt_b = length_of_b_list and f1.b in (...) Pavel On Fri, Oct 16, 2009 at 8:29 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > P Kishor <punk.k...@gmail.com> wrote: >> I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just >> an example. In reality, b could be any set of numbers, not just two >> numbers). To illustrate -- >> sqlite> SELECT * FROM foo WHERE b = 3; >> a b >> ---------- ---------- >> 2 3 >> 3 3 >> 5 3 >> sqlite> SELECT * FROM foo WHERE b = 4; >> a b >> ---------- ---------- >> 3 4 >> 5 4 >> >> So, I really want only the following rows >> 3 3 >> 5 3 >> 3 4 >> 5 4 > > select * from foo where a in > (select a from foo where b=3 > intersect > select a from foo where b=4) > and b in (3, 4); > > Or something like this - it scales to longer lists: > > select * from foo f1 where > (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and > f2.b in (...) )) = > length_of_b_list > and b in (...); > > Igor Tandetnik > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
