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)
||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