By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (clientid int, subclientid varchar(100));
insert into test values
(44, '1'),
(44, '9'),
(44, 'BASE'),
(44, '2'),
(45, 'BASE'),
(45, '2'),
(46, 'BASE'),
(46, '1');
select clientid
from (
select clientid,
case when subclientid in ('BASE', '1') then 1 else 2 end as score
from test
) x
group by clientid
having sum(score) = 2;
clientid |
---|
46 |