Count Memberships of User in Groups

I've a tree structure of an association which is divided in divisions, subdivisions etc. on every level users may have memberships to certain roles.

I want to count the memberships on every "structure type" (association, division, subdivision) as defined in the table

The Table structure looks like:

table intern_structures
Contains the hierarchy (nested set, but that does not matter here)
id | intern_structure_type_id |       name       | parent_id | lft |  rgt
 1            1                   My Company USA       0        1      6
 2            2                      Texas             1        2      5
 3            3                      El Paso           2        3      4        

table intern_structure_types 
Contains Description to the types like "association", "division", "subdivision"
id |     name
 1     Association
 2     Division
 3     Subdivision


table memberships
Contains the memberships 
id | user_id | intern_structure_id | role_id
 1      1              1                1
 2      1              2                2
 3      2              3                1
 3      2              3                3
 ....      

table roles
Contains role descriptions
id |  name 
 1    Admin
 2    Moderator
 3    Clerk

I want a grouped list like:

structure_type_name     | role_name      | count of memberships
    Association            Admin                   1
    Association            Moderator              10
    Association            Clerk                   0   << !! I miss the zero rows!
    Division               Admin                   7
    Divison                Moderator              43
    Division               Clerk                  31
    Subdivision            Admin                 234
    Subdivision            Moderator             942
    Subdivision            Clerk                 456

What I achieved so far is this query:

SELECT 
    is_types.name,
    roles.name,
    COUNT(memberships.id)
FROM
    roles,
    intern_structure_types AS is_types
        LEFT JOIN intern_structures AS is_elements ON is_elements.intern_structure_type_id = is_types.id    
        LEFT JOIN memberships ON memberships.intern_structure_id = is_elements.id
WHERE
    roles.id = memberships.role_id 
GROUP BY
    is_types.id, roles.id

It works fine except that it doesn't list all roles because some roles don't have any 开发者_如何学运维memberships yet but I want them listed as well just with 0 as membership count.

I'd be very thankful for any help!

I'm assuming the counts you showed in the OP are contrived. To get the results you want, you should create a derived table of the types and roles in use and then left join that entire query to a cross join of the roles and types.

Select  is_types.name
    , roles.name
    , Count(Z.is_type_name) 
From roles
    Cross Join intern_structure_types As is_types
    Left Join   (
                Select is_types.name As is_type_name
                    , roles.name As role_name
                From intern_structures As is_elements 
                    Join intern_structure_types As is_types
                        On is_types.id = is_elements.intern_structure_type_id
                    Join memberships
                        On memberships.intern_structure_id = is_elements.id
                    Join roles
                        On roles.id = memberships.role_id
                ) As Z
        On Z.is_type_name = is_types.name
            And Z.role_name = roles.name
Group By is_types.name, roles.name