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 to share