please help me in this query ( add condition to count())

[nasser bahaj]

hi all.

 

please i need your help for this mysql query

 

/////

table 1 (posts):

postid > auto-increment
catid > int
posttitle > varchar
posttext > text
active > int // 1=active , 0=inactive

//////

 

//////

table 2 : (categories):

catid > auto-increment
catname > varchar

//////:

 

so i want to list categories by category name and i want to put beside every category the number of posts in it . i used this query :

 

SELECT categories.catname,COUNT(posts.postid) AS numposts FROM categories LEFT JOIN posts ON categories.catid=posts.catid GROUP BY categories.catname

 

 

this query is working well but the problem is i want to count only the active posts (where active = 1 ) i want to add this condition to count , i tried this query .

 

SELECT categories.catname,COUNT(posts.postid) AS numposts FROM categories LEFT JOIN posts ON categories.catid=posts.catid WHERE posts.active=1 GROUP BY categories.catname

 

but the problem at this query it is select only the categories that has active posts while i want to select all categories but count only ative posts

 

thanks for your time ..

[kickstart]

Hi

 

You can put conditions in the ON clause, which might solve your issue.

 

SELECT categories.catname,COUNT(posts.postid) AS numposts 
FROM categories 
LEFT JOIN posts 
ON categories.catid=posts.catid 
AND posts.active=1
GROUP BY categories.catname

 

This should exclude the inactive posts before the JOIN. Checking in the WHERE clause excludes them after the JOIN, hence drops any rows where there are no matching records.

 

All the best

 

Keith

[nasser bahaj]

Mr kickstart

 

you are great

 

thank you very very much for help and your time ;