Am trying to find which rows are missing a particular worker_role based on existence of another worker_role value

Question

select category_name, event_name 
from security_events_view 
where category_name not in ('ABA Data Collection', 'ABA Session', 'MST Events') 
  and worker_role in ('Program_admin', 'Program_Leader')
having max(case when worker_role = 'Program_admin' then 1 else 0 end) = 0
order by category_name, event_name

But I am get an error:

Msg 8120, Level 16, State 1, Line 1
Column 'security_events_view.category_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The data would look like this:

Input

category_name    event_name        Worker_role
----------------------------------------------------
Home             Hair_Brushing     Program_leader
Home             Hair_Brushing     Program_admin
Home             Teeth_Brushing    Program_leader

With this input, we want to see

Home     Teeth_Brushing

as the result set. Since there is no row for worker_role = Program _admin

Answer 1

This should work:

select category_name, event_name 
from security_events_view 
where category_name not in('ABA Data Collection', 'ABA Session', 'MST Events') 
    and worker_role in ('Program_admin', 'Program_Leader')
having max(case when worker_role = 'Program_admin' then 1 else 0 end) = 0
group by category_name, event_name
order by category_name, event_name