Filter results by user role

I have a query that returns the total number of users per a specific meta value (gender), considering only users that are not blocked (user_blocked_access):

SELECT umc.meta_value AS label, COUNT(umb.user_id) AS total
FROM wp_users u
INNER JOIN
    wp_usermeta umc
    ON umc.user_id = u.ID 
    AND umc.meta_key = 'user_gender' 
    AND umc.meta_value <> ''
INNER JOIN
    wp_usermeta umb
    ON umb.user_id = u.ID 
    AND umb.meta_key = 'user_blocked_access' 
    AND CAST(umb.meta_value AS BINARY) <> '1' 
GROUP BY umc.meta_value
ORDER BY total DESC

I have to return only the results for a specific user role:

umb.meta_key = 'wp_capabilities' AND umb.meta_value LIKE '%craftsman%'

But don’t know where I should adds this part of code.

Answer

You can add an additional JOIN:

SELECT umc.meta_value AS label, COUNT(umb.user_id) AS total
FROM wp_users u INNER JOIN
    wp_usermeta umc
    ON umc.user_id = u.ID  AND
       umc.meta_key = 'user_gender' AND
       umc.meta_value <> '' INNER JOIN
    wp_usermeta umb
    ON umb.user_id = u.ID AND
       umb.meta_key = 'user_blocked_access' AND 
       CAST(umb.meta_value AS BINARY) <> '1' INNER JOIN 
    wp_usermeta umr
    ON umr.user_id = u.ID AND
       umr.meta_key = 'wp_capabilities' AND
       umr.meta_value LIKE '%craftsman%'
GROUP BY umc.meta_value
ORDER BY total DESC;

Note: This assumes that there is only one wp_capabilities per user. If there are multiples, then EXISTS is safer:

SELECT umc.meta_value AS label, COUNT(umb.user_id) AS total
FROM wp_users u INNER JOIN
    wp_usermeta umc
    ON umc.user_id = u.ID  AND
       umc.meta_key = 'user_gender' AND
       umc.meta_value <> '' INNER JOIN
    wp_usermeta umb
    ON umb.user_id = u.ID AND
       umb.meta_key = 'user_blocked_access' AND
       CAST(umb.meta_value AS BINARY) <> '1'
WHERE EXISTS (SELECT 1
              FROM wp_usermeta umr
              WHERE umr.user_id = u.ID AND
                    umr.meta_key = 'wp_capabilities' AND
                    umr.meta_value LIKE '%craftsman%'
             )
GROUP BY umc.meta_value
ORDER BY total DESC;