SQL SELECT transactions for items that two people have both purchased at least once

I have a table of transactions where a each transaction has a transaction_id, an item (what item was purchased in the transaction) and a customer_id (person who ordered the item).

select * from transactions

transaction_id, item, customer_id
1, Computer, 100
2, Computer, 101
3, Dog, 100
3, Dog, 103
4, Cat, 103
5, Carpet, 101

How can I pull all transactions only for items that have been purchased by both customer_id 100 and customer_id 101 at least once? In this case that result would only be the transactions with the item Computer.

Answer

You can use exists if you want only the transactions for those customers:

select t.*
from transactions t
where customer_id in (100, 101) and
      exists (select 1
              from transactions t2
              where t2.item = t.item and
                    t2.customer_id in (100, 101) and
                    t2.customer_id <> t.customer_id
             );

If you want all transactions, you could use window functions:

select t.*
from (select t.*,
             sum(case when customer_id = 100 then 1 else 0 end) over (partition by item) as num_100,
             sum(case when customer_id = 101 then 1 else 0 end) over (partition by item) as num_101
      from transactions t
     ) t
where num_100 > 0 and num_101 > 0;