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

this question has answer here:

• mysql pivot table query dynamic columns 3 answers

how can matrix table 2 related table 1 query statement picture shows? or have use temporary table instead?

here possible solution this, note solution current data structure, if there more roles in role table not proper solution. since inner query needs changed accommodate new roles.

select  id, name, coalesce(max(t.fw),'no') fw, coalesce(max(t.df),'no') df, coalesce(max(t.gk),'no') gk, coalesce(max(t.mf),'no') mf user u left join (   select    case      when r.id = 1 , ur.role_id not null 'yes'     else null   end `fw`,    case      when r.id = 2 , ur.role_id not null 'yes'     else null    end `df`,    case      when r.id = 3 , ur.role_id not null 'yes'     else null   end `gk`,    case      when r.id = 4 , ur.role_id not null 'yes'     else null   end `mf`,   user_id   role r   left join user_role ur on ur.role_id = r.id )t on t.user_id = u.id group u.id 

i added no null values , changed in statements like

coalesce(max(t.fw),'no') fw,

so instead of no can add ' '

demo