How to include worker with all roles

1

This is a staff based query where we want to see the worker roles each staff person has. The application allows to check off on "is_all_worker_roles" so in cases of admin, this is checked also others by mistake, But this query is not including those who only have that checked. Is there a way to include that condition, the worker without roles on the profile because they have the *all checked? I guess if a worker has no roles I would want to see that also. Hope this is clear -thanks guys!

select a.staff_id, a.worker_role_id, d.staff_name, b.is_all_worker_roles,
c.description
from staff_worker_role_link a
inner join staff b
on a.staff_id = b.staff_id
inner join worker_role c
on
a.worker_role_id = c.worker_role_id
inner join staff_view d
on b.staff_id = d.staff_id

2

Which table does this column belong to

is_all_worker_roles

3

HI that is in Staff table, not staff_view.

4

You provided almost no details / structure, but this is my best guess based on the info that is available:

select sw.staff_id, sw.worker_role_id, sv.staff_name, s.is_all_worker_roles,
wr.description
from staff s
inner join staff_worker_role_link sw
on sw.staff_id = s.staff_id
inner join worker_role wr
on
sw.worker_role_id = wr.worker_role_id or s.is_all_worker_roles = 1 --<<--<<--
inner join staff_view sv
on s.staff_id = sv.staff_id
1 Like