Given a table with two columns, A and B, with no constraints what would be the best way to query for those values of A such that there are corresponding values of B in a specified set.
For example, given this data, below, and ignoring the primary key, I would want the following results: for p values 10,11,12 ==> 1 for p values 11,12 ==> 2 for p values 10 ==> 3 For all other "input" we should get no result/null/whatever. CREATE TABLE t (id integer primary key, c, p); INSERT INTO t VALUES(1, 1,10); INSERT INTO t VALUES(2, 1,11); INSERT INTO t VALUES(3, 1,12); INSERT INTO t VALUES(4, 2,11); INSERT INTO t VALUES(5, 2,12); INSERT INTO t VALUES(6, 3,10); For all other "input" we should get no result/null/whatever. I can concoct a query based on the "input" like this but it seems like there must be a better way? SELECT DISTINCT c as C FROM t WHERE EXISTS (SELECT c FROM t AS x WHERE x.p = 10) AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11) AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12) AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3; _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users