如何在wordpress中执行子查询

Question

Hello I need to do a query that counts the posts that have meta_key like status and meta_value like Unplaced, and the same post has a meta_key like issue_date and meta_value like a date between two days. For example, I have a table like this:

id_meta post_id meta_key   meta_valuue
      1       1 status     Unplaced
      2       1 issue_date 20160428
      3       2 status     VIP
      4       2 issue_date 20160428
      5       3 status     Placed
      6       3 issue_date 20160427
      7       4 status     Unplaced
      8       4 issue_date 20160420

I want that the query only returns 1 because only one post has status Unplaced and the issue_date between 21 and 28. The query that I wrote is:

$results = $wpdb->get_var(" 
SELECT COUNT(*) 
  FROM wp_posts
     , wp_postmeta 
 WHERE meta.key = 'status' 
   AND meta_value LIKE '%Unplaced%' 
   AND post_id = ID 
   AND post_status <> 'trash' 
   AND post_id IN ( SELECT post_id 
                      FROM wp_postmeta 
                     WHERE meta_key = 'issue_date' 
                       AND meta_value >= '20160421' 
                       AND meta_value <= '20160428')
");

I need that the posts don't be in trash.

Thank you!!!!

Answer 1

E.g.:

SELECT s.post_id
     , s.meta_value status
     , i.meta_value issue_date 
  FROM wp_posts s 
  JOIN wp_posts i 
    ON i.post_id = s.post_id 
 WHERE s.meta_key = 'status' 
   AND s.meta_value = 'Unplaced' 
   AND i.meta_key = 'issue_date' 
   AND i.meta_value BETWEEN '20160421' AND '20160428';