Using CASE for an optional predicate

Question

Can someone help me with this request?

I am new to using CASE, how can I do this. If rights = manager, then I want to run the code in this 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

Thank!

Answer 1

You really don't need 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)

Answer 2

Write it this way

select
    email, user_id, first_name, last_name, rights
from
    users u
where
    company_id = 2141
    and
    receives_emails = 'y'
    and
    (
        (
            u.rights = 'manager'
            and
            user_id in (
                    select
                        user_id
                    from
                        manager_depts
                    where
                        company_id = u.company_id
                        and
                        dept_id = 2
                )
        )
        or
        (
            u.rights <> 'manager'
        )
    )