Check for events that have only one of two roles

1

In this data set, we are showing the worker_role and the events they have rights to including specific rights. For some reason they originally made 2 system admins.
System Administrator and Administrator. Whenever we made a new event, we were to give full and the same access to both worker_roles. Over time, this has not been folllwed. I know that sometimes the System Admin was given access and the Administor was not And Vice Versa. The category is not revelant which ever event has one should also have the other.

Event_name Worker_role Event Category Permissions
(read, edit, delete, undelete)
|A29i Request-Parental|Administrator|Consents|1|1|1|1|1|
||A29 iMonthly Summary|Administrator|Requirements for People|1|1|1|1|1|
|A29i Monthly Summary|System Administrator|Requirements for People|1|1|1|1|1|
|A29i Bike Forms |Administrator|Requirements for People|1|1|1|1|1|
|A29i Bike Forms |System Administrator|Requirements for People|1|1|1|1|1|
So for example the first record has the worker_role 'Administrator' and Not 'System Administrator'
I would want a display of such occurances and also the vice versa.
Here is the query : select distinct ed.event_name, wr.description, ec.category_name, se.has_access, se.is_add_allowed, se.is_edit_allowed,
se.is_delete_allowed, se.is_undelete_allowed from staff_view sv
inner join staff_worker_role_link_view sw
on sv.staff_id = sw.staff_id
inner join worker_role wr
on sw.worker_role_id = wr.worker_role_id

inner join security_events se
on sw.worker_role_id = se.worker_role_id

inner join event_definition ed
on se.event_definition_id = ed.event_definition_id

inner join event_category ec
on ed.event_category_id = ec.event_category_id

where end_date is null and sv.is_active = '1' and ed.is_active = '1'
and wr.description in ('Administrator', 'System Administrator')
order by ed.event_name

2

I believe this should give you the results you requested:

;WITH only_one AS (SELECT ed_.event_name
 FROM worker_role wr_
 INNER JOIN security_events se_ ON wr_.worker_role_id = se_.worker_role_id
 INNER JOIN event_definition ed_ ON se_.event_definition_id = ed_.event_definition_id
 WHERE ed_.is_active = '1'
 AND wr_.description IN ( 'Administrator', 'System Administrator' )
 GROUP BY ed_.event_name
 HAVING MIN(wr_.description) = MAX(wr_.description))
SELECT ed.event_name,wr.description,ec.category_name,se.has_access
,se.is_add_allowed,se.is_edit_allowed,se.is_delete_allowed,se.is_undelete_allowed
FROM staff_view sv
INNER JOIN staff_worker_role_link_view sw ON sv.staff_id = sw.staff_id
INNER JOIN worker_role wr ON sw.worker_role_id = wr.worker_role_id
INNER JOIN security_events se ON sw.worker_role_id = se.worker_role_id
INNER JOIN event_definition ed ON se.event_definition_id = ed.event_definition_id
INNER JOIN event_category ec ON ed.event_category_id = ec.event_category_id
INNER JOIN only_one o ON o.event_name=ed.event_name
WHERE end_date IS NULL AND sv.is_active = '1' AND ed.is_active = '1'
AND wr.description IN ( 'Administrator', 'System Administrator' )
ORDER BY ed.event_name;

I couldn't tell which table end_date belonged to, so I left it out of the CTE definition.