Querying MySQL result set for existence of a particular value

Question

MySQL 8.x here. I have the following tables:

[users] (users in the system)
===
user_id
user_username
user_password_enc
user_first_name
user_last_name

[events] (CRUDdable things the users can participate in)
===
event_id
event_name
event_date
event_location

[event_roles] (coarse-grained, event-specific roles that users can be assigned to)
===
event_role_id
event_role_name
event_role_label    -- ex: EVENT_ADMIN, EVENT_REP, EVENT_FOLLOWER

[event_permissions] (granular permissions that get assigned to roles)
===
event_permission_id
event_permission_name
event_permission_label    -- ex: CAN_VIEW_LOCATION, CAN_CHANGE_DATE, CAN_CHANGE_LOCATION, CAN_CANCEL_EVENT

[event_permissions_x_roles] (crosswalk table mapping permissions to roles)
===
event_permissions_x_role_id
event_permission_id
event_role_id

[event_user_roles] (which users are assigned to which event roles)
===
event_user_role_id
event_id
user_id
event_role_id

I now want to write a query that asks the following question:

Does user_id = 234 have CAN_CHANGE_LOCATION permission to event_id=123?

My best attempt thus far:

SELECT
  event_permission_label
FROM
  event_permissions_x_roles
INNER JOIN
  event_permissions
ON
  event_permissions_x_roles.event_permission_id = event_permissions.event_permission_id
WHERE
  event_role_id = (
    SELECT
      event_role_id
    FROM
      event_user_roles
    WHERE
      user_id = 234
      AND
      event_id = 123
  );

However this will only give me a list of event_permission_labels that the user_id=234 has for event_id=123.

That's close to what I want, but I want my query to go further and check that list to see if CAN_CHANGE_LOCATION is part of that list. I don't know if there's a way to write this so that it returns a boolean true/false (yes user does have the desired permission for the given event; or no the user does not, etc.). Can anyone spot where I'm falling a bit short?

Answer 1

Schematically:

SELECT EXISTS ( SELECT NULL
                FROM {joined tables set}
                WHERE t1.user_id = 234 
                  AND t2.event_permission_label = 'CAN_CHANGE_LOCATION'
                  AND t3.event_id = 123 )