How to retrieve user data based on role using SQL?

Add following in WHERE condition of your SQL query. 

WHERE t1.meta_key = "<?php echo $wpdb->prefix; ?>capabilities" 
AND ( t1.meta_value LIKE '%ROLE_YOU_WANT_TO_FILTER%')

Let me know if it works for you or not.

Related Posts:

  1. WordPress Database Huge wp_usermeta Table
  2. How to migrate a database from a server to another
  3. DB prefix not updating
  4. I wanted to Know that how can I give ingore to 0000-00-00 in my config file
  5. WordPress character set issue
  6. What is “Advanced” SQL?
  7. Oracle SQL query for Date format
  8. WPDB Insert or if exists Update
  9. Where are image paths to product category images stored in WordPress database
  10. Sharing database for collaborative development
  11. Organizing WP Pages based on old website database
  12. Inserting Post Meta From SQL
  13. Moving an entire wordpress site from one domain to a new domain and database
  14. Change SQL get_results to search for posts with custom term in custom taxonomy
  15. Would manually deleting the dumping data fix a “#1062 – Duplicate entry ‘1’ for key ‘PRIMARY'” phpMyAdmin error?
  16. Delete all post meta except featured image
  17. wpdb query on custom table not working [closed]
  18. How to execute mulitple statement SQL queries using $wpdb->query?
  19. Delete duplicate rows from wordpress database where a column is duplicate in phpmyadmin
  20. Query WordPress database by registered date and role
  21. Using table lock with wp_insert_post?
  22. How to Move Local Singlesite Database to Remote Multsite Database?
  23. Scheduling posts in database
  24. Set Display Name to first and last name (phpmyadmin SQL Query)
  25. Size article thumbnails and retrieve them directly from database
  26. How to replace values in WordPress DB using phpMyAdmin
  27. How to query the WordPress database to get posts of a certain custom post type, taxonomy and field?
  28. Delete all post meta except featured image Using SQL
  29. wpdb_prepare with multiple or condition
  30. Uppercase to sentence case for post titles
  31. SQL Error wp_commentmeta – incorrect table definition
  32. phpMyAdmin displays error when importing database
  33. change wp user status from wp_users table
  34. How do I get the posts within a certain year/date(with sql query)?
  35. WordPress database import: Row size too large
  36. Export / import WordPress database
  37. WordPress is not creating table in database after activating plugin,
  38. Bulk-create posts from SQL database
  39. Difficulty importing my live site to local
  40. Setup private content for specific users with daily updates [closed]
  41. Connection refused on locahost after importing database?
  42. Which data is written and stored in the database when read-only (non-posting, non-commenting) users visit the site?
  43. Insert Query not working in the form
  44. WPDB SQL query with prepare() returning variable, not db value
  45. Problems with moving my WordPress site to another domain/server
  46. Changing root password in PHPMyAdmin for WordPress Database when going live
  47. Post Views / Hit Counter Problem?
  48. Why this query is not showing any result on wordpresss home page?
  49. pull data from a remote wordpress database
  50. Clear Terms from Taxonomy for Specific Post IDs?
  51. Is my way to change WordPress server is correct?
  52. Why does changing Database Table Prefixes result in loss of content?
  53. Options on how to Display member specific data from a MS SQL database
  54. SQL query to set posts in bulk based on the post content
  55. Delete all posts that do NOT have a specific tag
  56. Location of WP default mySQL tables creation file?
  57. SELECT SQL_CALC_FOUND_ROWS with wordpress search
  58. Files on Localhost, Database on Server
  59. Backing up WordPress database and files
  60. $wpdb->get_var – What value is returned when zero records are found?
  61. How to display data from db in select list [closed]
  62. wp_option table error while importing
  63. Installing local to live WP into subdirectory
  64. WordPress SQL – How to Check for Category?
  65. Can’t log into wordpress site – I have made a new user and still cant access
  66. Is there any way to monitor a MySQL db and check what has changed?
  67. Counting post and changing roles in mysql database. Query not code
  68. Deleting empty meta key values
  69. Sql query to get all metadata for a post
  70. I imported an restore an database and It shows #1067 – Invalid default value for ‘user_registered’
  71. Job and Employee Performance Tracking with Product Safety
  72. I need to find posts that contain YouTube links without http:// or https:// prefix and add http:// to all of them
  73. Is there any defects for using same database prefix in WordPress for multiple sites?
  74. Migrating meta_key and meta_value from old theme to new one
  75. SQL database import #1046 error
  76. What is recommended data type and encryption type for password in a database
  77. syntax error for changing user role from database
  78. How do I loop/iterate through posts to edit all img tags?
  79. What’s the easiest way to connect external database and display data on wordpress?
  80. Duplicate database to roll back theme & content changes
  81. Is there a way for two deferent themes to consume two different DB on a same WP instance?
  82. How to set posts to draft in bulk based on the content of the post
  83. 2 $wpdb queries causing error Table ‘wp_postmeta’ is specified twice, both as a target for ‘UPDATE’
  84. Delete all users meta that named: user_avatar
  85. Correct and secure way to access a custom SQL database in a custom PHP template file
  86. How to get specific table by current user login
  87. The MySQL Connection could not be established., before it was joomla but i have change it into worpress
  88. $wpdb not working
  89. Missing latest posts and options after DB migration via phpMyAdmin
  90. Check if a database is well installed
  91. Options table – where does my values go?
  92. How to add data from wordpress tables to new database tables you have created
  93. Getting deleted users in database
  94. Install WordPress with SQL database
  95. Migrated to Namecheap, now Trouble with Database & wp-config [closed]
  96. SQL query : Select a thumbnail for a media
  97. why you don’t Implement PDO for access every thing to database
  98. SQL Query to get post_id from wp_posts and and meta_key(s) from wp_postmeta
  99. SQL query to rewrite all media URLs to end with .webp
  100. Weird WP -Cli Error Connection Refused