My lack of SQL skills has stopped me in my tracks here.

I'm trying to select post_id, post_title and two postmeta values - the first has the meta_key of 'search_country' and the second a meta_key of 'search_region'.

I think I have a working solution for retrieving just the search_country:

 $querystr = "
       SELECT $wpdb->posts.ID as id, $wpdb->posts.post_title as title, $wpdb->postmeta.meta_value as country
       FROM $wpdb->posts, $wpdb->postmeta
       WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
       AND $wpdb->postmeta.meta_key = 'search_country' 
       AND $wpdb->posts.post_status = 'publish' 
       AND $wpdb->posts.post_type = 'films'
       AND $wpdb->postmeta.meta_value IS NOT NULL
       ORDER BY $wpdb->posts.post_title ASC

    $festivals = $wpdb->get_results($querystr, OBJECT);

However, I dont know how to change this to get the search_region also?

I've never got on with SQL :)

1 Answer 1


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'
    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.

  • Thanks doublesharp that is perfect! Its actually good that it only selects when there is data for both search_country and search_region as I need both anyway. I need to read up on joins now :)
    – Chris
    Commented Apr 4, 2013 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.