How To Make SQL Queries With Multiple AND Conditions On Same Column in SQL

Recently, a colleague approached me to help with building an SQL query. It was an interesting problem in my opinion and the solution to it is quite generic and can be applied to many similar problems.

My colleague had a table of producers and consumers, where consumer to producer was a many-to-many relationship. She wanted to get all consumers which interacted with at least all the producers from a given list.

This is a sample table called mytable:

In this table given a list of producers p1 and p2, the output should be consumers c1, c2 and c3. c4 will not be in the output because it only interacted with p1 but not with p2. Also c3 will be in the output because it’s enough that it interacts with p1 and p2 even though c3 also interacts with p3.

1. SQL solution
2. PostgreSQL solution

SQL solution

The first instinct is to use SQL IN operator, something like:

SELECT * FROM mytable
WHERE producer IN ('p1', 'p2')

However this will not produce correct results because SQL IN operator is just a shorthand for multiple OR conditions. However, we really need multiple AND conditions. Multiple AND conditions can’t be used on the same column, therefore after IN is used, we can first group by consumer and then make sure that the number of rows equals the number of producers in the input:

SELECT consumer FROM mytable
WHERE producer IN ('p1', 'p2')
GROUP BY consumer
HAVING COUNT(*) = 2

The above query correctly returns the list of consumers (c1, c2 and c3). It’s important to note that if the combination of consumers and producers wasn’t unique, then DISTINCT would need to be added into rows count:

SELECT consumer FROM mytable
WHERE producer IN ('p1', 'p2')
GROUP BY consumer
HAVING COUNT(DISTINCT producer) = 2

Finally, in order to get the rows of mytable which satisfy the original condition the following query can be executed:

SELECT * FROM mytable
WHERE consumer IN (
    SELECT consumer FROM mytable
    WHERE producer IN ('p1', 'p2')
    GROUP BY consumer
    HAVING COUNT(*) = 2
)

You can run the sample query in this playground link.

PostgreSQL solution

If you’re using PostgreSQL there’s another solution using array_agg aggregate function. First a list of producers can be aggregated per each consumer using array_agg. Secondly, @> contains operator can be used to check whether input array of producers is contained in the result of array_agg:

SELECT * FROM (
  SELECT consumer, array_agg(producer) producers FROM mytable
  GROUP BY consumer
) AS t WHERE t.producers @> '{p1, p2}'

You can run the sample query in this playground link.