2 LEFT JOINS count in the request

Question

I have a download component and want the categories to display subcodes and the number of items in advance. 1 of both works, but when trying to get both results, the result is multiplied by both.

The code I'm using is:

CHOOSE a. *,
 count (b.parentid) AS catscount,
count (c.id) AS itemscount
FROM (jos_foc_downl_categories AS LEFT JOIN jos_foc_downl_items AS c ON c.catid = a.id)
  LEFT JOIN jos_foc_downl_categories AS b ON b.parentid =
WHERE a.parentid = 0
GROUP BY a.id

This leads to a category with 4 subcategories and 5 files, including 20 for catscount and 20 for items.

What happened to this? Thank!

Answer 1

, . DISTINCT :

SELECT a.*,
count(DISTINCT b.parentid) AS catscount,
count(DISTINCT c.id) AS itemscount
....