加入两个表后用于打印标题和计数ID的SQL语句

Question

SELECT b.post_title
     , a.post_id
     , Deriv1.Count
FROM  wp_posts b
    , wp_postmeta a
LEFT OUTER JOIN (    
   SELECT meta_value, COUNT( * ) AS Count
   FROM  wp_postmeta
   GROUP BY meta_value
   ) Deriv1 
ON a.post_id = Deriv1.meta_value
WHERE a.meta_value = 1
  AND a.meta_key = 'type-select' 
  AND b.post_status = 'publish'
  and post_type = 'car-cc'

Current problem of the SQl STATEMENT is when i supply meta_value =1 it is fetching all values i.e. post_id which do not have meta_value =1 is also listed.

http://sqlfiddle.com/#!2/1115a7/1

Answer 1

I think the problem is you're not joining on the wp_posts table and wp_postmeta table. Add this to your WHERE clause:

AND a.post_id = b.ID

Alternatively, just do an INNER JOIN on the two tables:

SELECT b.post_title
     , a.post_id
     , Deriv1.Count
FROM  wp_posts b INNER JOIN 
        wp_postmeta a ON a.post_id = b.ID
LEFT OUTER JOIN (    
   SELECT meta_value, COUNT( * ) AS Count
   FROM  wp_postmeta
   GROUP BY meta_value
   ) Deriv1 
ON a.post_id = Deriv1.meta_value
WHERE a.meta_value = 1
  AND a.meta_key = 'type-select' 
  AND b.post_status = 'publish'
  and post_type = 'car-cc'

BTW -- Did you want the count for each group? If so, your query can be simplified:

SELECT b.post_title, a.post_id, COUNT( * ) as Total
FROM  wp_posts b INNER JOIN 
            wp_postmeta a ON a.post_id = b.ID
    WHERE a.meta_value = 1
      AND a.meta_key = 'type-select' 
      AND b.post_status = 'publish'
      and post_type = 'car-cc'
GROUP BY b.post_title, a.post_id

Here is some updated fiddle. Wasn't sure of your desired output.

Good luck.