输出表中的所有数据并显示哪些行属于用户

Question

I have a roles table which contains various user roles.

A pivot tables joins this to my users table.

users
id | name

roles
id | title

user_role
user_id | role_id

I would like to display all roles that exist and pre tick the ones that belong to a specific user.

How would I go about this, I take it I cannot do this in mySQL?

I was considering getting all roles with one query, then with another query get all roles that belong to a specific user.

Then loop through all roles and if there is a match with the specific users roles, output a checked box instead of an unchecked one.

Is there a better way?

Answer 1

Something like

SELECT R.id as role_id,
       R.title as role_title,
       UR.user_id as user_id,
       U.name as user_name
  FROM Roles R
  LEFT JOIN user_role UR
         ON UR.role_id = R.id
        AND UR.user_id = :myuserid
  LEFT JOIN users U
         ON U.id = UR.user_id

should return a complete list of roles, with either a NULL or the user id in the user column to indicate any roles that the user has been granted (user identified by id as :myuserid)