CODEWITHSUNDEEP
mysqlleft-join
Bert
Bert

Reputation: 855

2 LEFT JOINS count in a query

I have a download-component and want the categories to display the subcats and item count in advance. 1 of both works, but when trying to get both, the result is the multiply of both.

The code I use:

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

This results for a category with 4 subcategories and 5 files in the number 20 for catscount and 20 for itemscount.

What's wrong with this? Thanks!

Upvotes: 3

Views: 852

Answers (1)

Mark Byers
Mark Byers

Reputation: 838416

You're counting all rows, including duplicated values. Use DISTINCT to only count each distinct value once: 

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

Upvotes: 7

Related Questions