Using CASE for an optional predicate

Question

Can someone help me with this query?

I am new to using CASE, how can I make this work. If rights = manager then I want to run the code within the case.

select email, user_id, first_name, last_name, rights
from users u
where company_id = 2141
    and receives_emails = 'y'
    case u.rights when 'manager' then
        and user_id in (select user_id from manager_depts where company_id = u.company_id and dept_id = 2)
    end

Thanks!

Answer 1

You actually do not need a CASE here,

SELECT email, u.user_id, first_name, last_name, rights
FROM users u
     LEFT JOIN manager_depts d ON d.company_id = u.company_id and d.dept_id = 2
WHERE company_id = 2141 AND receives_emails = 'y'
  AND (u.rights != 'manager' OR d.user_id IS NOT NULL)