Exclude whole group based on single value

Question

Table T

CID Code
1    A
1    B
1    C
2    C
2    A
2    B
3    A
3    D

I want the output to have only those CID groups where Code <> 'C'

Desired Output:

CID  Code
3     A
3     D

Query Attempt:

SELECT DISTINCT
         CID
        ,Code
FROM Table T
WHERE Code <> 'C'

But this will exclude rows where Code = C, not the entire groups where Code = C. Not sure how GROUP BY could work here

Answer 1

One method uses not exists:

select distinct cid, code
from t
where not exists (select 1
                  from t t2
                  where t2.cid = t.cid and t2.code = 'C'
                 );

Another method uses a window function:

select distinct cid, code
from (select t.*,
             sum(case when t.code = 'C' then 1 else 0 end) over (partition by cid) as numCs
      from t
     ) t
where numCs = 0;

Answer 2

One more variant, which produces the same execution plan as the one with NOT EXISTS:

SELECT T.CID, T.Code
FROM T
WHERE
    T.CID NOT IN
    (
        SELECT T2.CID
        FROM T AS T2
        WHERE T2.Code = 'C'
    )
;

Inner query gives a list of all CID groups that have at least one C. Main query returns all rows from all other groups, not included in the inner list.

I don't understand why you put DISTINCT in the query. If the data doesn't have duplicates, DISTINCT would do extra sort that does nothing useful, since there are no duplicates already. If the data has duplicates - do you really want to get rid of them?