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
