How to get post_id by multi meta_value with sql query

3 Views

if have a post, 

id = 10

meta_key = size
meta_value = xxl

meta_key = color
meta_value = red

how can i get this post_id with sql like this

$my_post_id = $wpdb->get_results($wpdb->prepare( " SELECT post_id FROM $wpdb->postmeta WHERE meta_value = 'xxl' "),'ARRAY_A');
Post Views: 3

Related posts

Leave a Reply

1 comment

  1. Your base query for that would be 

    SELECT post_id 
  FROM wp_postmeta 
 WHERE meta_key   IN ('size', 'color')
   AND meta_value IN ('xxl', 'red')
 GROUP BY post_id
HAVING COUNT(DISTINCT meta_key)   = 2 
   AND COUNT(DISTINCT meta_value) = 2

    Here is SQLFiddle demo