SQL Query to Get Users With Role Subscriber

Question

I haven't got any luck with my SQL query to get users with role subscriber:

SELECT 
      ID, 
      display_name 
FROM 'wp_users' 
INNER JOIN 'wp_usermeta' ON 'wp_users'.ID = 'wp_usermeta'.user_id 
WHERE 'wp_usermeta'.meta_key = 'wp_capabilities' 
AND ('wp_usermeta'.meta_value LIKE 'subscriber') ORDER BY display_name

Can any body help me?

Answer 1

I have got the answer to my question:

SELECT wp_users.ID, wp_users.user_nicename 
FROM wp_users INNER JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id 
WHERE wp_usermeta.meta_key = 'wp_capabilities' 
AND wp_usermeta.meta_value LIKE '%subscriber%' 
ORDER BY wp_users.user_nicename

If anybody struggling with the same issue please use my SQL query above.

Answer 2

Here's a slight variant of @qqruza's answer that includes the user's email and role and returns users for all roles.

SELECT wp_users.ID, wp_users.user_nicename, wp_users.user_email, wp_usermeta.meta_value 
     FROM wp_users 
     JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
     WHERE wp_usermeta.meta_key = 'wp_capabilities'
     ORDER BY wp_users.user_nicename

If you have a WordPress multisite installation, to get the roles for all child sites, use:

SELECT wp_users.ID, wp_users.user_nicename, wp_users.user_email, wp_usermeta.meta_key, wp_usermeta.meta_value 
     FROM wp_users 
     JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
     WHERE wp_usermeta.meta_key LIKE 'wp_%capabilities'
     ORDER BY wp_users.user_nicename

Of course, you'll need to look at the wp_usermeta.meta_key value to determine which child site (blog) the record applies to.