Search code examples
sqloracle-databasehaving-clause

Oracle SQL: Distinct list of results based row not having column value

I'm trying to limit my rows based on column values, but having difficulty getting the syntax right.

Given:

create table acctprefs (acctid char(5),
                        prefcode char(3));

insert into acctprefs values ('10000', 'ABC');
insert into acctprefs values ('10000', 'DEF');
insert into acctprefs values ('10000', 'GHI');

insert into acctprefs values ('10001', 'ABC');
insert into acctprefs values ('10001', 'DEF');
insert into acctprefs values ('10001', 'GHI');
insert into acctprefs values ('10001', 'ZZZ');

I would like to return a distinct list of accounts that do not have the 'ZZZ' preference. In this case, I'm trying to get a result that would be simply:

10000

I'm able to get the distinct accounts with 'ZZZ' with the query below, but I just need the opposite.

select *
from 
  acctprefs ap
where
  ap.prefcode in 
    (select ap.prefcode from acctprefs ap group by ap.prefcode having(ap.prefcode = 'ZZZ'));
Solution

  • One approach is aggregation with a having clause:

    select ap.acctid
from acctprefs ap
group by ap.acctid
having sum(case when ap.prefcode = 'ZZZ' then 1 else 0 end) = 0;

    The having clause counts the number of ZZZ for each account . . . and returns only those with zero.