I've mixed up the adding orders to make sure they have no affect on the
outcome - and in the final results examples I've left all the columns so
you can see what is going on, but you of course need only one of the
columns in your desired output.
CREATE TABLE t (id integer primary key, c, p);
INSERT INTO t VALUES(1, 1,11);
INSERT INTO t VALUES(2, 1,12);
INSERT INTO t VALUES(3, 1,10);
INSERT INTO t VALUES(4, 2,11);
INSERT INTO t VALUES(5, 2,12);
INSERT INTO t VALUES(6, 3,10);
-- Base Query:
WITH SETS(PSet, PContent, PCount) AS (
SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
;
-- PSet | PContent | PCount
-- ------------ | -------- | ------
-- 1 | 10,11,12 | 3
-- 2 | 11,12 | 2
-- 3 | 10 | 1
-- Example one - finding the set that contains all of 10,11,12 and
nothing else:
WITH SETS(PSet, PContent, PCount) AS (
SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
WHERE PContent = '10,11,12'
;
-- PSet | PContent | PCount
-- ------------ | ------------ | ------------
-- 1 | 10,11,12 | 3
-- Example two: Finding any set that contains 11,12:
WITH SETS(PSet, PContent, PCount) AS (
SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
WHERE PContent LIKE '%11,12%'
;
-- PSet | PContent | PCount
-- ------------ | -------- | ------
-- 1 | 10,11,12 | 3
-- 2 | 11,12 | 2
On 2018/12/04 6:17 AM, Mark Wagner wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
