Wordpress WPDB SQL - How to query two meta_key

Question

I am trying to query two meta keys at once using my code:

SELECT * FROM wp_postmeta

WHERE meta_key = "listing_location_enabled"
AND meta_value = "yes"

AND meta_key = "listing_location_country"
AND meta_value = "INDIA"

But is not showing any results. Is my query correct?

Answer 1

No, your query is not correct. (Those meta tables are a notorious pain in the xxx neck to use correctly.) You need this query pattern. It joins wp_postmeta to itself.

SELECT lle.post_id,
       lle.meta_value listing_location_enabled.
       llc.meta_value listing_location_country
 
  FROM wp_postmeta lle
  JOIN wp_postmeta llc  ON lle.post_id = llc.post_id
 WHERE lle.meta_key = 'listing_location_enabled'
   AND lle.meta_value = 'yes'
   AND llc.meta_key = 'listing_location_country'
   AND llc.meta_value = 'INDIA'

Or, better yet if you're writing code for WordPress, use its WP_Meta_Query facility.

$q = new WP_Query( array(
    'meta_query' => array(
        'relation' => 'AND',
        'lle' => array(
            'key' => 'listing_location_enabled',
            'value' => 'yes',
        ),
        'llc' => array(
            'key' => 'listing_location_country',
            'value' => 'INDIA',
        ), 
    )
) );