By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table test (clientid int, subclientid varchar(100));
insert into test values
(44, '1'),
(44, '9'),
(44, 'BASE'),
(44, '2'),
(45, 'BASE'),
(45, 'BASE'),
(45, '2'),
(46, 'BASE'),
(46, 'BASE'),
(46, '1');
10 rows affected
select clientid
from (
select clientid,
case when subclientid in ('BASE', '1') then 1 else 2 end as score
from (select distinct clientid, subclientid from test) t
) x
group by clientid
having sum(score) = 2;
| clientid |
|---|
| 46 |
select clientid,
case when subclientid in ('BASE', '1') then 1 else 2 end as score
from (select distinct clientid, subclientid from test) t
| clientid | score |
|---|---|
| 45 | 2 |
| 44 | 1 |
| 46 | 1 |
| 45 | 1 |
| 44 | 2 |
| 46 | 1 |
| 44 | 2 |
| 44 | 1 |