SQL query: how to filter a many to many relationship

I have ONE table, contains a clientID and a subclientID, how to filter this to retrun clients that contain BASE and 1 ONLY. In this case, I would like to return client 46

clientID	subclientID
44	1
44	9
44	BASE
44	2
45	BASE
45	2
46	BASE
46	1
46	1

EDIT: subclient may contains duplicates

Answer

Here’s one way of finding that:

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;

Explanation

  select clientid,
    case when subclientid in ('BASE', '1') then 1 else 2 end as score
  from test

will change BASE and 1 to the score 1. Everything else will be given score 2. The result will be like this:

clientid |  score
-------: | -----:
      44 |  1
      44 |  2
      44 |  1
      44 |  2
      45 |  1
      45 |  2
      46 |  1
      46 |  1

Then, for each clientid we sum up the score. If the score is exactly 2, we know we want that client id.

Examples

• https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1f845cfbb15657283c6758c186daf1c3
• https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3984ebea44659fee444f1502ecd5ee61
• https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=3984ebea44659fee444f1502ecd5ee61

Edit – if you have duplicates

If you have duplicates, you can use this:

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;

Example: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=29590e13e0603bc675167950da05114b