So reading your example I think you mean Given a table with two columns, c and p, with no constraints what would be the best way to query for those values of c such that the set of corresponding values of p is equal to a specified set.
If p isn't text then I'd say something using group_concat select c from t group by c having group_concat(p, '|') = '10|11|12'; Only problem being that group_concat is non-deterministic(?) in the sense that the order in which it gets the values matters, and while you might get it to work with an "implementation detail" of a specific release to give it the values in the right order, it's not in spec and might change. So ...from (select * from t order by p) group by c ... might work now, but also might not work later. I "think" there's a way to do it with the fancy new window function functionality which will guarantee ordered, but haven't figured that out yet. On a tangent: There are no links to the "Window Functions" page (https://www.sqlite.org/windowfunctions.html) from any of the following at the moment: "SQLite Documentation": (https://www.sqlite.org/docs.html) "Query Language Understood by SQLite": (https://www.sqlite.org/lang.html) "SQLite Query Language: SELECT": (https://www.sqlite.org/lang_select.html) There's only a single, one word link on the Expression page (https://www.sqlite.org/lang_expr.html) Would it be feasable to get some links added in some of those spots? -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Mark Wagner Sent: Monday, December 03, 2018 11:17 PM To: SQLite mailing list Subject: [sqlite] how to 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
