How can I get a matrix table from two related table by one query statement?

Question

How can I get a matrix table from two related table by one query statement like the picture shows? or do I have to use a temporary table instead?

Answer 1

I would just use a CROSS JOIN between the user and role tables to get every possible combination, and LEFT JOIN the User_Role table to get matches where one exists.

This would give one returned row per user / role combination. This could then be output easily in a table in whatever language you are using.

SELECT User.id AS user_id, User.name, Role.role, IF(User_Role.role_id IS NULL, NULL, 'yes')
FROM User
CROSS JOIN Role
LEFT OUTER JOIN User_Role
ON User.id = User_Role.user_id
AND Role.id = User_Role.role_id
ORDER BY User.id, Role.id

Answer 2

Here is a possible solution for this, note that the solution is for the current data structure, if there are more roles in the role table then its not a proper solution. Since the inner query will needs to be changed to accommodate new roles.

select 
id,
name,
coalesce(max(t.FW),'No') as FW,
coalesce(max(t.DF),'No') as DF,
coalesce(max(t.GK),'No') as GK,
coalesce(max(t.MF),'No') as MF
from user u
left join (
  select 
  case 
    when r.id = 1 AND ur.role_id is not null then 'Yes'
    else null
  end `FW`,

  case 
    when r.id = 2 AND ur.role_id is not null then 'Yes'
    else null 
  end `DF`,

  case 
    when r.id = 3 AND ur.role_id is not null then 'Yes'
    else null
  end `GK`,

  case 
    when r.id = 4 AND ur.role_id is not null then 'Yes'
    else null
  end `MF`,
  user_id
  from role r
  left join user_role ur on ur.role_id = r.id
)t
on t.user_id = u.id
group by u.id

I added No for null values , this could be changed in the statements like

coalesce(max(t.FW),'No') as FW,

So instead of No can just add ' '

DEMO