Custom query, multiple custom keys

Question

I need to get posts that have two meta-values.

It tried the following code, but this is getting empty result

<?php
 $featuredquery = "
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id
    AND (wpostmeta.meta_key = 'article_level' AND wpostmeta.meta_value = 'Hovedsak')
    AND (wpostmeta.meta_key = 'article_genre' AND wpostmeta.meta_value = 'X6.no (Spill og underholdning)')
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wposts.post_date < NOW()
    ORDER BY wposts.post_date DESC
    LIMIT 0 , 4
 "; 

 $featuredposts = $wpdb->get_results($featuredquery, OBJECT);
?>

Any ideas for this? I tried searching around, but didn't find anything that suited me.. :/

Thanks for all help

Answer 1

I'm not an SQL expert, but I would think you might have to JOIN twice to pick two distinct rows out of wp_postmeta:

 $featuredquery = "
    SELECT wposts.*
    FROM $wpdb->posts wposts
    JOIN $wpdb->postmeta article_level ON
      ( wposts.ID = article_level.post_id AND
        article_level.meta_key = 'article_level' )
    JOIN $wpdb->postmeta article_genre ON 
      ( wposts.ID = article_genre.post_id AND
        article_genre.meta_key = 'article_genre' )
    WHERE article_level.meta_value = 'Hovedsak'
    AND article_genre.meta_value = 'X6.no (Spill og underholdning)'
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wposts.post_date < NOW()
    ORDER BY wposts.post_date DESC
    LIMIT 0 , 4
 "; 

Answer 2

Similar to the new tax_query option to query_posts(), WordPress 3.1 will add a meta_query argument:

query_posts( array(
    'meta_query' => array(
        array(
            'key' => 'article_level',
            'value' => 'Hovedsak',
        ),
        array(
            'key' => 'article_genre',
            'value' => 'X6.no (Spill og underholdning)',
        ),
    ),
) );

Not quite here yet, but worth mentioning.