Select from wp_post and multiple meta_value from wp_postmeta

2 Views

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

Read More

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 🙂

Post Views: 2

Related posts

Leave a Reply

1 comment

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