Wordpress custom query to select meta value

Question

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 

He currently selects post title and post id , but I also need to select meta value , where meta key = type-gen problem is another meta key already being compared in the request.

SQL Fiddle: http://sqlfiddle.com/#!2/109c2/1

Answer 1

Here you go

 SELECT b.post_title, a.post_id, COUNT( * ) AS Total, (SELECT meta_value FROM `wp_postmeta` WHERE post_id= b.ID AND meta_key='type-gen') AS 'new meta value' 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 

Fiddle

Answer 2

 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') OR (a.meta_value = 1 AND a.meta_key = 'type-gen' AND b.post_status = 'publish' and post_type = 'car-cc') GROUP BY b.post_title, a.post_id 

Answer 3

Use WP_Query for this,

Changed on the Codex page

 $args = array( 'post_type' => 'car-cc', 'meta_query' => array( array( 'key' => 'type-select', 'value' => '1', 'compare' => '=' ), array( 'key' => 'type-gen', 'value' => 'my-great-type', 'compare' => 'LIKE' ) ) ); $query = new WP_Query( $args );