Use joined value in where clause

I want to use a where statement on a value from a joined table. Can't figure it out:

SELECT item.*, count(articles.authorid) AS articlecount 
FROM #_authors AS item 
LEFT JOIN #_articles AS articles ON (articles.authorid = item.id) 
WHERE item.published=1 
AND articlecount>3 
ORDER BY articlecount DESC

The orderby works fine, but when I add "AND articlecount>3" I get th开发者_Go百科e error: "#1054 - Unknown column 'articlecount' in 'where clause'".

What is the best way to include the WHERE on articlecount? Thanks in advance!

For aggregate columns, you use HAVING, like this:

SELECT item.*, count(articles.authorid) AS articlecount 
FROM #_authors AS item 
LEFT JOIN #_articles AS articles ON (articles.authorid = item.id) 
WHERE item.published=1 
GROUP BY 1,2,3,4,5,6,7,8 -- assuming there are 8 columns in item
HAVING count(articles.authorid) > 3 
ORDER BY 9 DESC -- assuming there are 8 columns in item

 SELECT item.*, count(articles.authorid) AS articlecount 
    FROM #_authors AS item 
    LEFT JOIN #_articles AS articles ON (articles.authorid = item.id) 
    WHERE item.published=1 
    AND articlecount>3 
    ORDER BY articlecount DESC


articlecount refers to which table field?
if articlecount  is in #_author table then you have to write item.articlecount 

or articles.articlecount

Try out using HAVING to filter by aggregated valeus:

WHERE item.published=1
HAVING count(articles.authorid) > 3
ORDER BY articlecount DESC