CODEWITHSUNDEEP
mysqlwordpress
Jeff Purcell
Jeff Purcell

Reputation: 71

Mysql Query returning 0 results

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 )

Upvotes: 3

Views: 134

Answers (2)

Strawberry
Strawberry

Reputation: 33935

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.

Upvotes: 0

Beto
Beto

Reputation: 3438

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.

Upvotes: 2

Related Questions