I have an sqlite table that relates 2 entities, in the form

x | y
1   1
1   3
2   2
2   3
3   1
3   2
3   3

Expected output: 3 (because x=3 links to y=1,2,3, aka all values of y)

I need to select all values in x that have a relation to every value in y My issue is that when I group by x, then check how many y-values there are for a given group of x's, I don't know how many y values there are total because I just grouped my table by x's and can't access the original one to count.

CodePudding user response：

With this query:

SELECT x
FROM tablename
GROUP BY x
HAVING COUNT(DISTINCT y) = (SELECT COUNT(DISTINCT y) FROM tablename);

you can get get all the x that are related to every y.

You can use it with the operator IN to get all the rows from the table:

SELECT *
FROM tablename
WHERE x IN (
  SELECT x
  FROM tablename
  GROUP BY x
  HAVING COUNT(DISTINCT y) = (SELECT COUNT(DISTINCT y) FROM tablename) 
);

If there are no duplicate combinations of x and y, the HAVING clause can be written simpler:

HAVING COUNT(*) = (SELECT COUNT(DISTINCT y) FROM tablename)

See the demo.