Adding status to a query not working

Question

Ive been using a query to pull in the amount of posts from a given category no problem. But i now want to pull in from a given status but no matter what i try its just not working quite right.

This is the code that works without the status..

{exp:query sql="SELECT count(exp_category_posts.entry_id) AS post_count

FROM exp_category_posts

WHERE cat_id = {category_id}
"}{post_count}{/exp:query}

And i want to add in the status.. which ive tried with the following, but this isnt quite right,

{exp:query sql="SELECT count(exp_category_posts.entry_id) AS post_count

FROM exp_category_posts 

JOIN exp_channel_entries

WHERE cat_id = '{category_id}' AND status = 'open'
 "}{post_count}{/exp:query} 

Anyone any suggestions?

thanks B.

Answer 1

A JOIN requires an ON clause too. Also, it's exp_channel_titles, not exp_channel_entries. Try this on for size as the sql parameter:

SELECT COUNT(cp.entry_id) AS post_count
FROM exp_category_posts cp
JOIN exp_channel_titles t
ON cp.entry_id = t.entry_id
WHERE t.status IN ('Open', 'open')
AND cp.cat_id = '{category_id}'

If you want to add more statuses to the list, just add them inside the brackets, ie ('Open', 'open', 'draft', 'foo').