Select from wp_post and multiple meta_value from wp_postmeta

You need to join the post_meta table twice to the posts table, once for each meta value:

SELECT p.ID id, p.post_title title, country.meta_value country, region.meta_value region
FROM {$wpdb->posts} p
JOIN {$wpdb->postmeta} country 
    ON p.ID = country.post_id AND country.meta_key = 'search_country'
JOIN {$wpdb->postmeta} region
    ON p.ID = region.post_id AND region.meta_key = 'search_region'
WHERE 
    p.post_status="publish"
    AND p.post_type="films"
ORDER BY p.post_title ASC

I would recommend aliasing the tables (p, country, and region) to make it a little easier to read as you are joining the the same table twice. If you wanted another meta_value at the same time, you can join to the post_meta table additional times.

By using JOIN you are only returning results where the post has a value for both meta keys… if you wanted to allow one to be NULL you would LEFT JOIN the table instead.

Related Posts:

  1. Pagination with custom SQL query
  2. How to make an activities stream mixing posts and comments?
  3. What is an efficient way to query based on post_meta?
  4. How To Get Some Data From WordPress Database Using WordPress $wpdb Query?
  5. Query post with meta_query where date is not in future
  6. How to correctly pass values to wpdb->prepare()?
  7. $wpdb select date range of posts
  8. Which is faster wpdb & get_row or wp_query & ge_post_meta?
  9. How to pass orderby params to $wpdb->prepare()?
  10. Make a SQL query with wpdb in WordPress
  11. WordPress query posts with multiple post_meta data
  12. List of ways to access WordPress database?
  13. Passing a SQL query to the WP Query
  14. Any way to use FETCH_KEY_PAIR with $wpdb?
  15. What argument does my function need to echo get_results() query results
  16. Include post_status check within $wpdb query
  17. WP Query to order posts by multiple meta fields
  18. wpdb LIKE request shows all database data
  19. Convert a WP Query into a simpler SQL query to fetch only COUNT of posts
  20. WP_Query with “post_title LIKE ‘something%'”?
  21. How to only display posts whose meta_value field is not empty?
  22. How to print the excuted sql right after its execution
  23. Can wp_query return posts meta in a single request?
  24. order by numeric value for meta value
  25. How do I query for posts by partial meta key?
  26. Return only Count from a wp_query request?
  27. Use REGEXP in WP_Query meta_query key
  28. WordPress retrieving meta data for all custom post types in list view
  29. Order by optional meta key?
  30. meta_query: using BETWEEN with floats and/or casting to DECIMAL
  31. Whats the difference between post_limits and pre_get_posts?
  32. Reduce or prevent calling of update_meta_cache
  33. Using OR conditions in meta_query for query_posts argument
  34. Is there a way to extend WP_query so Custom Post Types can have properties?
  35. how does $wpdb differ to WP_Query?
  36. What is the most efficient way of querying posts based on visits and date for current day?
  37. Is it possible to wrap Geo Location search around WP_Query?
  38. Getting attachments by meta value
  39. WP_Query displaying ALL posts
  40. Is it true $wpdb->get_results is faster than WP_Query in most cases?
  41. Is it possible to select against a post’s parent’s fields with WP_Query?
  42. Query causing load because of SQL_CALC_FOUND_ROWS post counting?
  43. meta_query where value is equal to given value
  44. Query Custom Meta Value with Increment
  45. Extending WP_Query — Optimise SQL query
  46. WP-CLI How to generate a list of posts with corresponding meta values
  47. Use WP_Query with a custom SQL query
  48. How to filter sql only for a specific post type
  49. SQL Statement generated by WP_Query not producing expected results
  50. How can I connect to another WP database and use WP_Query?
  51. WP_Query not working as expected for attachments and custom meta_query
  52. SQL query equivalent to WP User Query
  53. How to count post meta key values for all posts in database
  54. Group posts by meta_key
  55. How should I use posts_where to change meta_value from a string to integer?
  56. Recommended way to drop a pending query (in pre_get_posts)?
  57. How can I create a WP_Query that returns posts where one meta_value
  58. how to retrieve specific product attribute value in an sql query?
  59. WordPress altering my custom query, How to fix it?
  60. WP Meta Query for some meta (array) values
  61. Get number of comments after modifying comment query
  62. How to change a custom query into a standard loop?
  63. WP Query post meta value
  64. Would this post meta be better added to the post table rather than post_meta table
  65. Custom URl parameter
  66. query posts in functions.php and update a field
  67. Compile meta values from custom loop into array and then calculate sum total
  68. How to get sum of meta_values of a meta_key in wp_query according to conditions
  69. how to fire join query with post_meta
  70. How to count data records in wordpress which have same meta_value in wp_postmeta table?
  71. Best approach to create Hot and Trending sections
  72. WP_Query, custom sort and custom filter
  73. Should close $wpdb via $wpdb->close()
  74. Filter WordPress posts by between parameter
  75. SQL User Query by Multiple Roles using PHP
  76. Advanced WP Query hogs the SQL server
  77. Order posts by meta value and Date
  78. SELECT * FROM $wpdb->posts WHERE ID > 160
  79. Efficient way to update multiple post meta
  80. $wpdb->insert inserting two rows in database
  81. How to make orderby ‘meta_value_num’ OPTIONAL?
  82. Get the Plugin Which Triggered a MySql Query in WordPress?
  83. Query Posts depends on custom field inside repeater field using acf
  84. Detect featured image among the attached images
  85. How would I format a query that depends on post parent taxonomy
  86. Minimising Database Queries when using Advanced Custom Fields
  87. Mysql query LIKE not working
  88. WP User Query get all authors with last name starting with specific letter
  89. Get meta info related to current post
  90. Pull post meta with post_query?
  91. Order by custom table and multiplication
  92. Sort users by “birthday” using WP_User_Query
  93. Group WP_Query by meta_key date
  94. What’s the purpose / logic of split_the_query in WP_Query->get_posts
  95. WPDB query – decrypting DB data
  96. WP_query posts closest to todays date
  97. How To Fix WP Query Returns Results But Shouldn’t?
  98. exclude posts with a specific custom field and value
  99. Get Current User Id Inside a Loop Returns 0 For a Shortcode
  100. List of the years with posts presented