Select rows that contain a range of values while excluding values from other columns

Question

Hitting a small wall with a query here. trying to see if transactions contain type 01 while excluding transactions that contain item 23 or 25.

here's a reprex.

In SQL fiddle

create table purchases (
  transaction_id int,
  item int,
  type int,
  customer char(1)
  
);

insert into purchases values (1, 23, 01, "A");
insert into purchases values (1, 25, 01, "A");
insert into purchases values (2, 23, 01, "B");
insert into purchases values (2, 25, 01, "B");
insert into purchases values (2, 1, 01, "B");
insert into purchases values (3, 3, 01, "A");
insert into purchases values (4, 23, 01,"B");
insert into purchases values (4, 25, 01,"B");
insert into purchases values (5, 23, 01,"A");
insert into purchases values (6, 4, 02,"C");
insert into purchases values (7, 9, 03,"C");

Here's the query to identify transactions that only have items 23 and 25 but nothing else, it works, (should be transactions, 1,4 & 5).

select transaction_id from purchases where item in (23,25)
and transaction_id not in (select transaction_id from purchases where item not in (23,25));

However, when I'm struggling to single out the transactions that have type 01 but not items 23 and 25.

I tried this, but it gives out transactions 2 & 3 when it should only be 3 since 2 does contain items 23 & 25.

here's the query I was going with, based on the first one.

select * from purchases where type = 1 and transaction_id not in (select transaction_id from purchases where item in (23,25)
and transaction_id not in (select transaction_id from purchases where item not in (23,25)));

expected result

transaction_id  item    type    customer
      3          3       01        A
                       

Answer 1

Based on your updated question, i'd suggest you use the NOT EXISTS clause like below

select * from purchases p1 where not exists
(
select 1 from purchases p2 where p1.transaction_id=p2.transaction_id 
and p2.item in (23,25))
and type=1

fiddle demo link

Answer 2

I see that you have already changed the expected result in the question several times (while the query itself does not change), so I'm not sure what exactly you want to get. In any case, you can take this dbfiddle example, and using arrays, filtered by distinct sorted elements:

Answer 3

You want one row per transaction, so aggregate and GROUP BY transaction_id. Then use the HAVING clause and COUNT conditionally.

select transaction_id
from purchases
group by transaction_id
having count(*) filter (where item = 23) = 0
   and count(*) filter (where item = 25) = 0
   and count(*) filter (where type = 1) > 0
order by transaction_id;

Demo: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=520755370f13d41ba35ca12e7eb5277e

If you want to show all rows matching above transaction IDs:

select * from purchases where transaction_id in ( <above query> );

Answer 4

Here is one option

select p.*
from purchases p
join (
       select transaction_id
       from purchases
       group by transaction_id 
       having count(case when item in (25,23) then 1 end)=0
       and    count(case typ when 1 then 1 end)>0
      )x 
on p.transaction_id=x.transaction_id

For your sample data:

insert into purchases values (1, 23, 01, 'A');
insert into purchases values (1, 25, 01, 'A');
insert into purchases values (2, 23, 01, 'B');
insert into purchases values (2, 25, 01, 'B');
insert into purchases values (2, 1, 01, 'B');
insert into purchases values (3, 3, 01, 'A');
insert into purchases values (4, 23, 01,'B');
insert into purchases values (4, 25, 01,'B');
insert into purchases values (5, 23, 01,'A');
insert into purchases values (6, 4, 02,'C');
insert into purchases values (7, 9, 03,'C');

Result:

3   3   1   A