Intersection of Records in Postgres

Question

Suppose I have labels with multiple stores associated with them like so:

label_id | store_id
--------------------
label_1  | store_1
label_1  | store_2
label_1  | store_3
label_2  | store_2
label_2  | store_3
label_3  | store_1
label_3  | store_2

Is there any good way in SQL (or jooq) to get all the store ids in the intersection of the labels? Meaning just return store_2 in the example above because store_2 is associated with label_1, label_2, and label_3? I would like a general method to handle the case where I have n labels.

Answer 1

Then convert the query by @GMB into an SQL function that takes an array and returns a table of store_id's.

create or replace 
function stores_with_all_labels( label_list text[] )
 returns table (store_id text) 
 language  sql
as $$
    select store_id
      from label_store
     where label_id = any (label_list)
     group by store_id
    having count(*) = array_length(label_list,1);
$$;

Then all that's needed is a simple select. See complete example here.