SQLTeam.com | Weblogs | Forums

Query bringing in wrong events

HI Perhaps you can analyze this. I am trying to get all the events that a staff has access to. Here there is something off, it is bringing in events that the staff does NOT have access to as well. Staff_security_events_view has the worker_role_id and that should link to worker_role_id in staff_worker_role_link.

select sv.last_name, sv.First_name, sev.worker_role,
sev.event_name, sev.is_add_allowed, sev.is_edit_allowed, sev.is_delete_allowed, sev.is_undelete_allowed,
sev.is_unsign_allowed, ec.category_name, ec.is_people_event
from staff_view sv
inner join staff_worker_role_link swrl
on sv.staff_id = swrl.staff_id
inner join security_events_view sev
on swrl.worker_role_id =sev.worker_role_id
inner join event_definition ed
on sev.event_definition_id = ed.event_definition_id
inner join event_category ec
on ed.event_category_id = ec.event_category_id
where sv.end_date is null and ed.is_active = 1
order by sv.last_name, sv.first_name, sev.event_name

without any DDL and sample data, it'll be very difficult to see what is happening. Can you provide PKs on these tables too?? I would guess that the staff in Stage view is related to multiple rows in the staff_work_role_link table and then one of those roles has access to the event definition. So, long story short, my guess is your joins aren't correct. But it could be raining in Oklahoma, no idea, just a wild guess