Select clients that ordered only specific conferences and none else

Question

Imagine we have a client table and an order table, which are linked together like this: client.id = order.client_id. Order table entry contains a product id that has been purchased. Product table entry contains a conference id from which this product has been purchased.
How do I select clients that have only purchased products from a list of specific conferences?

I could solve this with a PHP script, making multiple requests, rearranging data but that is no fun.

I tried this so far:

select
    o.client_id,
    array_agg(p.conference_id) as conference_ids
from product as p
left join order as o
    on o.id = p.order_id
where
    p.conference_id = any('{ 46545, 46543 }'::int[])

But this didn't work as it selects clients that have purchased not only from these but from other conferences, too.

Edit: fixed sql to be syntactically correct

Answer 1

One way to approach this without using arrays would be:

select client.id
from product as p
left join order as o on o.id = p.order_id
group by client.id
having count(*) filter(where p.conference_id not in (46545, 46543)) = 0;

If you also wanted to assert that both of the above two conferences were also attended (i.e. attended both, but no others), you could add another assertion to the HAVING clause:

select client.id
from product as p
left join order as o on o.id = p.order_id
group by client.id
having count(*) filter(where p.conference_id not in (46545, 46543)) = 0 and
       min(p.conference_id) <> max(p.conference_id);