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

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

CodePudding user response：

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

CodePudding user response：

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> );

CodePudding user response：

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