How to return count of custom post type posts with a specific custom field value via $wpdb?

Question

I am trying to get count of a custom post type with a specific custom field value from wpdb without doing a WP_Query or get_posts since I think it is more resource efficient and I need to return 10 different counts for 10 different values on one page.

I can't get the right count to show up. A number is returned, but it is wrong. Here is the code :

$meta_value = key($choices);
        $count = $wpdb->get_var(
            $wpdb->prepare (
            "
            SELECT  count(ID) 
            FROM    $wpdb->posts, $wpdb->postmeta
            WHERE   $wpdb->posts.post_status = 'publish'
                    AND $wpdb->posts.post_type = 'tasks'
                    AND $wpdb->postmeta.meta_key = 'task_status'
                    AND $wpdb->postmeta.meta_value = %s
            ",
            $meta_value
        )); ?>

How can I make it right? key($choices) returns the right value (string) for the $meta_value variable. It is my absolute first use of $wpdb so have mercy.

Edit: solved by adding INNER JOIN:

"
        SELECT  count(ID) 
        FROM    $wpdb->posts
        INNER JOIN $wpdb->postmeta
            ON $wpdb->posts.ID = $wpdb->postmeta.post_id
        WHERE   $wpdb->posts.post_status = 'publish'
                AND $wpdb->posts.post_type = 'tasks'
                AND $wpdb->postmeta.meta_key = 'task_status'
                AND $wpdb->postmeta.meta_value = %s
        "

Answer 1

While custom SQL is sometimes what it takes, note that to address your concern for WP_Query it supports 'fields' => 'ids' argument to limit data returned to IDs (which can be easily used for count).

In many cases that is reasonably efficient while more robust and future-proof in WP environment.