SQLTeam.com | Weblogs | Forums

I need to see where a particular column in not in another process

I want to get from this table, 2 columns, event_name and category name.
I want to know the columns where there is one worker record for 'program_leader', but there is no similar record (event_name and category_name) for 'program_admin'
select category_name, event_name from security_events_view where
category_name not in('ABA Data Collection', 'ABA Session', 'MST Events'
) and worker_role = 'Program_Leader'
order by category_name

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


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

thank you, I am just getting this error, I am not sure how to resolve.
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.

Oops, forgot to include the GROUP BY:

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')
group by category_name, event_name
having max(case when worker_role = 'Program_admin' then 1 else 0 end) = 0
order by category_name, event_name