Mysql Query returning 0 results

2 Views

Given the values in these 2 rows, why does my query return 0 rows and how can the query be rewritten to work properly? 

meta_id post_id meta_key   meta_value
   1422      73 wpcf-milk      22
   1423      73 wpcf-mw        -7

SQL Query:

   SELECT * FROM wp_postmeta
    WHERE post_id = 73 
    AND ( meta_key = 'wpcf-mw' AND meta_value BETWEEN -8 AND 200 )
    AND ( meta_key = 'wpcf-milk' AND meta_value BETWEEN 20 AND 200 )
Post Views: 2

Related posts

Leave a Reply

2 comments 

  1. SELECT * FROM wp_postmeta 
WHERE post_id = 73 
  AND (( meta_key = 'wpcf-mw' AND meta_value BETWEEN -8 AND 200 ) 
  **OR** ( meta_key = 'wpcf-milk' AND meta_value BETWEEN 20 AND 200 ))

    Change AND by OR.

  2. There are essentially two ways of doing this. One is roughly as follows:

    SELECT post_id
     , MAX(CASE WHEN meta_key = 'wpcf-milk' THEN meta_value END) wpcf_milk
     , MAX(CASE WHEN meta_key = 'wpcf-mw' THEN meta_value END) wpcf_mw 
  FROM wp_postmeta 
 GROUP 
    BY post_id 
HAVING wpcf_milk BETWEEN 20 AND 200 
   AND wpcf_mw BETWEEN -8 AND 200;

    The other approach involves OUTER JOINs and is slightly faster, but slightly more tedious to type out – so I’ll leave it to others.