Left join exclude with multiple constraints

Question

I am trying to isolate customers that only had a certain type of transaction (cash, card, cheque) on any given day. They may have had multiple transactions on a date but I want to exclude those that had a certain type.

My table called transaction stores the customer number, transaction date, transaction type, amount. I want to find the customer and date if the only transaction (or transactions) are of the type 'card'.

So the following data would only return: 3456, 26/03/2018

+----------+------------+------+--------+
| Customer |    Date    | Type | Amount |
+----------+------------+------+--------+
|     1234 | 26/03/2018 | Card |     10 |
|     1234 | 26/03/2018 | Cash |     20 |
|     1234 | 28/03/2018 | Cash |     20 |
|     2345 | 26/03/2018 | Cash |     20 |
|     2345 | 28/03/2018 | Cash |     20 |
|     3456 | 26/03/2018 | Card |     10 |
|     3456 | 26/03/2018 | Card |     20 |
+----------+------------+------+--------+

This is my code but it only returns blank rows.

select t1.customer, t1.date
from transaction t1 
left join transaction t2 on t1.customer = t2.customer  
and t1.date = t2.date  
where t1.type = 'card' and t2.type <> 'card'  
and (t2.customer is null  and t2.date is null)  group by t1.customer, t1.date

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-03-25T01:00:04

I would suggests aggregation and having:

select t.customer, t.date
from transaction t 
group by t.customer, t.date
having min(t.type) = max(t.type) and min(t.type) = 'card';

Here is a db<>fiddle illustrating that it works.

Left join exclude with multiple constraints

1 Answers