How to select categories and count the number of products in each category?

Question

I am trying to select the categories of the database and count in the same query the number of products registered in that category. So far so good, the problem is that I can not return to the category when there is no registered product, for example:

SELECT t1.id,t1.nome,t1.imagem,COUNT(*) AS Quantidade_produtos 
FROM categorias_produtos AS t1 
INNER JOIN produtos AS t2 ON t2.ref=t1.id 
GROUP BY t1.id

I have the categories:

categoria1
categoria2
categoria3

Products:

produto1 -> categoria1
produto2 -> categoria1
produto3 -> categoria2

I would like the result of the query to be more or less like this:

ID |   Nome   | Quantidade_produtos
 1 |categoria1|         2
 2 |categoria2|         1
 3 |categoria3|         0

However the query does not return this:

ID |   Nome   | Quantidade_produtos
 1 |categoria1|         2
 2 |categoria2|         1

You are ignoring the categories that have no registered product:

 3 |categoria3|         0

Any hint to be able to list all categories with the amount of products?

Answer 1

Instead of INNER JOIN with the product table do LEFT JOIN:

SELECT t1.id,t1.nome,t1.imagem,COUNT(t2.*) AS Quantidade_produtos
FROM categorias_produtos AS t1
LEFT JOIN produtos AS t2 ON t2.ref=t1.id GROUP BY t1.id

Because the LEFT JOIN will take everything as long as it contains the record in the table on the left, since in Ner it would have to have the id in the two tables in order to return a result for that row and when it does not have the record in the product table, it will ignore this line if it is used Inner Join, so the use of left Join is correct