How to implement counting products in a category?

Question

PHP

There are two tables: one with products, the second with categories.
The first table has a products_cat field that stores a link to the sub_name field in the second table.
As a result, I pull out the list of subcategories through the query:

SELECT * FROM categories  where left_key >:left and right_key <:right ORDER BY name

Thus, I get a list of all subcategories, but I still need to calculate the number of products in each of them.
What would be the best way to implement this. Perhaps there is an option to both tables or use nested queries?

Answer 1

count group by ?