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.