n this query - I am trying to see which rows have a worker_role.description (wr.description) of 'Administrator' but not 'System Administrator' and vice versa. I am not sure if it's a subquery or how to do the subquery. I am not sure where to place the subquery or how to code with multi tables.
select se.is_active,ed.event_name, wr.description, se.has_access, se.is_add_allowed, se.is_edit_allowed, se.is_delete_allowed, se.is_undelete_allowed,
se.is_unsign_allowed from security_events se
join event_definition ed
on se.event_definition_id = ed.event_definition_id
join worker_role wr
on se.worker_role_id = wr.worker_role_id
where se.is_active = '1'
order by ed.event_name
the data looks like this: I. is_active event_name description has_access is_add_allowed is_edit_allowed is_delete_allowed
1 6 month chk Administrator 1 1 6 month chk Clinician 1
in the above(I.) I would want to report that System Admin is not a role on this event_name. II.
is_active event_name description has_access is_add_allowed is_edit_allowed is_delete_allowed 1 3 month chk System Administrator
1 1 3 month chk Clinician 1
In the above (II.) I would want to report that 'Administrator' is not on this list.
the description is great but confusing at the same time. Can you provide DDL, sample data and what you are expecting as a result, then we can help. From what you provided, I can't tell which value belongs in which field.
the data is this: We are concerned with Event_name and worker_role. In this example (I.) we are not interesed in this event_name. Why? Because we are intereste in example II> because we want to know, which event_name only has 1 of the 2 worker roles as a row, the worker_roles are 'Administrator' and 'System Administrator' Why? Because we need the events to have them both.
I.
Event_name Worker_role
|1- Counseling|Administrator|1|1|1|1|1|0||
I give data every time. It is not easy to understand that is a different point. I am trying to improve in this area. I work at the help desk and am trying to do report projects.