Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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 :)

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Apr 4 '13 at 19:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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