There are two ways to use conditions when you use JOIN. You can either add the condition in the JOIN xxx ON or you can add them in the WHERE part. I think (but don't know for sure) that one of these 3 soloutions works as you want:
// As much as possible in the JOIN
SELECT t1.id, t1.category_title, COUNT(t2.id) as cnt
FROM categories t1
LEFT JOIN posts t2
ON t2.post_category = t1.id
AND t2.content_published=1
WHERE t1.content_type=1
GROUP BY t1.id
// Nothin in the JOIN
SELECT t1.id, t1.category_title, COUNT(t2.id) as cnt
FROM categories t1
LEFT JOIN posts t2
WHERE t1.content_type=1
AND t2.post_category = t1.id
AND t2.content_published=1
GROUP BY t1.id
// Middel alternative
SELECT t1.id, t1.category_title, COUNT(t2.id) as cnt
FROM categories t1
LEFT JOIN posts t2
ON t2.post_category = t1.id
WHERE t1.content_type=1
AND t2.content_published=1
GROUP BY t1.id
I don't have SQL here, so I can't check it out at the moment. I don't think that the second alternative work, and I think that the first and last will give the exact same result. But since I can't check it at the moment it's better to give you all, then you can check it out yourself.
