Only return exceptions from multiple fields in a table

Question

I have table (transactions) that stores retail transactions. I want to be able to find unique combinations of several fields. I have a table that stores transaction type (Online or Store) and the customer name, week number and product type. I need to find either online or store transactions that share the same customer, week and product but only if there isn't a corresponding transaction from the other transaction type. See my sample below ...

+------------------+----------+------+--------------+
| Transaction_Type | Customer | Week | Product_Type |
+------------------+----------+------+--------------+
| Online           | 123      | 1    | Clothing     |
+------------------+----------+------+--------------+
| Store            | 123      | 1    | Homeware     |
+------------------+----------+------+--------------+
| Online           | 123      | 1    | Homeware     |
+------------------+----------+------+--------------+
| Online           | 123      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Store            | 123      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Online           | 123      | 2    | Sporting     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Store            | 345      | 2    | Homeware     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Homeware     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Store            | 345      | 2    | Homeware     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Sporting     |
+------------------+----------+------+--------------+

From this I want to see the following returned ...

+------------------+----------+------+--------------+
| Transaction_Type | Customer | Week | Product_Type |
+------------------+----------+------+--------------+
| Online           | 123      | 1    | Clothing     |
+------------------+----------+------+--------------+
| Online           | 123      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Clothing     |
+------------------+----------+------+--------------+
| Store            | 345      | 2    | Homeware     |
+------------------+----------+------+--------------+
| Online           | 345      | 2    | Sporting     |
+------------------+----------+------+--------------+

The other transactions are excluded because there is the same customer, week and product_type values for both Online and Store.

The code I have so far is this ...

select * from transactions
group by customer, week, product_type
having count(customer, week, product_type) <> 2

But I think I should be using partitions but I don't know how.

Thanks

Answer 1

You can use not exists:

select t.*
from transactions t
where not exists (
    select 1
    from transactions t1
    where 
        t1.customer = t.customer
        and t1.week = t.week
        and t1.product = t.product
        and t1.transaction_type <> t.transaction_type
)