PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Querying postgres for distinct values of A where B has all values of a given set

I have a table with two columns like this:

Column A Column B
1 1
1 2
1 3
2 1
2 2
3 1
3 3

How do I query in postgres "Give me the distinct values of A such that have B contains both [1, 2] when A is a constant?" The expected result of such a query is A = [1, 2] from the simple table above.

I have been reading the postgres documentation and have written some queries, but I feel like I am missing something obvious here.

This is coming from a ManyToMany relation’s join table and I am also thinking about revisiting the relation there — perhaps I am looking at this in a more complicated way than it needs to be. But I have use cases for both query sides, e.g.:

"Given a set of B, give me all instances of A that contain all given values of B"

as well as

"Given a set of A, give me all the instances of B that are related to A"

Tags:

2

Answers


  1. 0

    This is a common query, and one canonical approach uses aggregation:

    SELECT A
FROM yourTable
WHERE B IN (1, 2)
GROUP BY A
HAVING COUNT(DISTINCT B) = 2;

    For the exact case of checking for only 2 distinct B values, we could also have written the HAVING clause as:

    HAVING MIN(B) <> MAX(B)

    and this might be sargable (i.e. can use an index on the B column).

    Login or Signup to reply.
  2. 0 
        SELECT "Column A"
FROM your_table_name
GROUP BY "Column A"
HAVING COUNT(DISTINCT CASE WHEN "Column B" IN (1, 2) THEN "Column B" END) = 2;

    COUNT(DISTINCT CASE WHEN "Column B" IN (1, 2) THEN "Column B" END) counts the distinct values of "Column B" that are either 1 or 2.

    The = 2 condition ensures that the group has both values 1 and 2 in "Column B".
    This query will return the distinct values of "Column A" for which all specified values of B (1 and 2) are present.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search