Oracle SQL CASE with a LIKE returning a value for both scenarios when I only want a result if they have any of the criteria

Question

I have a really simple scenario I just cannot get to work.

I have some sample data:

MyTable

IDNO IDCode
1    AAA
1    BBB
1    CCC
2    BBB
2    CCC
3    AAA

Current Code

SELECT DISTINCT IDNO,
CASE WHEN IDCode IN ('BBB', 'CCC') THEN 1 ELSE 0 END yesno
FROM MyTable

Current Output

IDNO    yesno
1       0
1       1
2       1
3       0

What I want is to return a 1 if any of the criteria is met, even if they contain a value that isn't in the CASE criteria but contain at least 1 of them.

Expected Output

IDNO    yesno
1       1
2       1
3       0

Answer 1

Try below using group by and max aggregation

DEMO

SELECT IDNO,
max(CASE WHEN IDCode IN ('BBB', 'CCC') THEN 1 ELSE 0 END) yesno
FROM MyTable
group by IDNO

Answer 2

If you have a table of idno uniquely per row, then you can do:

select i.idno,
       (case when exists (select 1 from t where t.idno = i.idnot and t.idcode in ('BBB', 'CCC' then 1 else 0 end)
             then 1 else 0
        end) as flag
from ids i;

Although conditional aggregation is a fine solution, I mention this because:

• You may have idnos that have no idcodes at all, and this includes them.
• Exists often performs better than aggregation.
• This can fit better in a complex query where you are bringing together lots of attributes at the idno level.