MySQL: Possible to apply the OR operator across multiple selected rows?
I have three MySQL tables: users, roles and positions.
The users table is pretty self-explanatory. The roles table is a list of
job titles a person might hold, such as janitor, president, manager, etc.
The roles table also has a long array of boolean permissions, such as
access_basement or user_directory_access. If the role has has that bit
value set to false (or "0") that role lacks that permission.
Where it gets tricky is that a user might have multiple roles, hence why
they are connected by the positions table, which is simply a pairing of
the userId and roleId fields. So if I perform a query like:
SELECT * FROM users
LEFT JOIN positions ON users.userId=positions.userId
LEFT JOIN roles ON roles.roleId=positions.roleId
WHERE users.userId=123
I might get results like:
+---------+-----------+-----------------+-----------------------+
| name | title | basement_access | user_directory_access |
+---------+-----------+-----------------+-----------------------+
| Bob | Janitor | true | false |
+---------+-----------+-----------------+-----------------------+
| Bob | President | false | true |
+---------+-----------+-----------------+-----------------------+
Since Bob has two roles, but has different access with each, I'd like to
combine the results with a since MySQL query and the logical OR operation
across all rows, resulting in a table like:
+---------+-----------------+-----------------------+
| name | basement_access | user_directory_access |
+---------+-----------------+-----------------------+
| Bob | true | true |
+---------+-----------------+-----------------------+
So the question is: is it possible to apply the OR operator across
multiple selected MySQL rows?
Thanks!
No comments:
Post a Comment