SQL Query for multiple values and multiple keys in the same table

Question

Every post has two post meta: year and month (custom)

I need SQL query to pull all posts that have

wp_postmeta.meta_key = year AND wp_postmeta.meta_value = 2013

AND AT THE SAME TIME another meta_key / meta_value pair

wp_postmeta.meta_key = month AND wp_postmeta.meta_value BETWEEN 1 AND 12 ;

SELECT * FROM wp_postmeta
WHERE (meta_key = 'agam_post_options_year' AND meta_value = 2013)
    OR (meta_key = 'agam_post_options_month' AND meta_value BETWEEN 0 AND 12 )
GROUP BY meta_value;

This is something I tried and a couple of more variations but it doesn't do much... this specifically gives me one post that has year 2013 and 12 posts that have either 1 or ... to 12 for month field}

Could you show us what you have tried so far? Where's your code? — consuela
– consuela, Commented Nov 11, 2014 at 9:05
Thanks for adding your code. Edit your original post and add the code there instead of adding a comment :). — consuela
– consuela, Commented Nov 11, 2014 at 9:12
If you want to filter data for both conditions, you should use AND instead of OR, because you'll get any rows from 2013 or any month (between 1 and 12) which should return the whole table. Or maybe I didn't understand exactly what you're lookin' for... — qdev
– qdev, Commented Nov 11, 2014 at 9:40

v.sheldeshov · Accepted Answer · 2014-11-11 10:13:26Z

1

Please replace _PUT_YOUR_POST_ID_FIELD_HERE_ by something appropriate field from that table and try to run...

SELECT Y.meta_value, M.meta_value FROM wp_postmeta as Y
JOIN wp_postmeta AS M USING (_PUT_YOUR_POST_ID_FIELD_HERE_) 
WHERE (Y.meta_key = 'agam_post_options_year' AND Y.meta_value = 2013)
AND (M.meta_key = 'agam_post_options_month' AND M.meta_value BETWEEN 0 AND 12 )
GROUP BY Y.meta_value, M.meta_value;

answered Nov 11, 2014 at 10:13

v.sheldeshov

1887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shabany Over a year ago

OK I will be marking this as the correct answer. The only thing I did was modified it a bit SELECT Y.*, M.* FROM wp_postmeta as Y JOIN wp_postmeta AS M USING (post_id) WHERE (Y.meta_key = 'agam_post_options_year' AND Y.meta_value = 2013) AND (M.meta_key = 'agam_post_options_month' AND M.meta_value BETWEEN 0 AND 12 ) GROUP BY Y.meta_value, M.meta_value ORDER BY M.meta_value+0 DESC So I get that DESC order.. however.. I noticed that it does not duplicate results... I had two posts with the same year and same month... now I don't see it... is there anything there that's preventing this ?

shabany · Accepted Answer · 2014-11-11 12:55:33Z

0

OK It was all good. The reason it took out the "duplicates" was because we didn't group by Y.meta_id and instead we used meta_value

answered Nov 11, 2014 at 12:55

shabany

8699 silver badges14 bronze badges

Comments

iEmanuele · Accepted Answer · 2014-11-11 17:32:22Z

If you don't really need a pure SQL query and you're getting these posts for using them in WordPress, you could use meta_query array passed as a part of args to WP_Query class or get_posts() function.

$args = array(
 'posts_per_page' => -1,
 'post_type'      => 'your-post-type', // default 'post'
 'meta_query'     => array(
      relation => 'AND', // default 'AND' it might be 'OR'
      array(
          'key'   => 'agam_post_options_year',
          'value' => '2013',
          'compare'=> '=',
          'type'  => 'NUMERIC' // see docs for more types
      ),
      array(
          'key'   => 'agam_post_options_month',
          'value' => array( 0, 12 ),
          'compare' => 'BETWEEN',
          'type'  => 'NUMERIC' // see docs for more types
      )
   )
);

$posts = get_posts( $args ); // returns an array of posts objects
//OR
$query = new WP_Query( $args ); // returns a new WP_Query object

Hope it helps! : )

Collectives™ on Stack Overflow

SQL Query for multiple values and multiple keys in the same table

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related