Sql query to group and filter results

Question

I need help with a logic in sql

I have two columns

Ticket no. Status

    T1      CC

    T1      CP

    T1      CR

    T1      CO

    T2      CP

    T2      CR

    T2      CO

I want to exclude the entire group of Ticket no. that has the status CC. So in this case, after I run the query I should only get 3 records (i.e. T2 records coz T2 doesn’t have a ticket status CC.

Can anyone help me with a simple sql query please.

Answer 1

One method is not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.ticket_no = t.ticket_no and t2.status = 'CC'
                 );

In Teradata, though, the qualify clause might be simpler:

select t.*
from t
qualify sum(case when status = 'CC' then 1 else 0 end) over (partition by ticket_no) = 0;