Clever Geek Handbook

Mysql Query returns 0 results

Given the values ​​in those two lines, why is my query returning 0 rows and how can the query be rewritten to work correctly?

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 )
+3


source to share


2 answers


There are two ways to do this. One of them is roughly the following:

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;


Another approach involves OUTER JOINs and is slightly faster but a little more tedious to type - so I'll leave it to others.

0


source 


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 to OR.

+2


source






More articles:


All Articles