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

CodePudding user response：

Aggregation is one option here:

SELECT clientID
FROM yourTable
GROUP BY clientID
HAVING MIN(subclientID) <> MAX(subclientID) AND
       COUNT(CASE WHEN subclientID NOT IN ('1', 'BASE') THEN 1 END) = 0;

CodePudding user response：

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

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