EllisLab text mark
Advanced Search
     
exp:sql query help status open only
Posted: 07 July 2010 02:55 PM
Joined: 2007-05-22
456 posts

If someone could help Iam a little stuck as Im not that sql minded. I am running a query that returns the number of items in a category but I need it to only return the open records in that category not all records. Can I do this?

The code at the moment is

{exp:query sql="SELECT COUNT(DISTINCT entry_id) AS my_count FROM exp_category_posts WHERE cat_id = '{category_id}'"}{my_count}{/exp:query} 

many thanks Adam

 
Posted: 07 July 2010 04:14 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-12-18
1680 posts
SELECT
    COUNT
(*) AS my_count 
FROM
    exp_category_posts 
AS p NATURAL JOIN
    exp_weblog_titles 
AS t
WHERE 
    p
.cat_id '{category_id}' AND
    
t.status 'open' 
 Signature 

EE Pro Network
eMarketSouth - full service web design, development, and SEO
ExpressionEngine consulting services - SQL queries, .htaccess rewrites, template optimization, jQuery/AJAX, and more!
our add-ons - please “favorite” and rate our products on devot:ee if you like our work. smile

 
Posted: 07 July 2010 05:52 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2007-05-22
456 posts

Thats brilliant Ender thank you and I actuallt understood what you did.

many many thanks

Adam