paradise-serv
New Member
I have got 2 tables: \[code\]categories\[/code\] and \[code\]products\[/code\].Categories have parent-child relationship structure and the data is fetched by joining the same table.When fetching the data, I have to count: [*]how many products each category contains which have \[code\]stock >= 1\[/code\][*]how many subcategories a category contains which contain at least 1 product with \[code\]stock >= 1\[/code\]\[code\]\[/code\]\[code\]SELECT c. * , count( DISTINCT s.cat_id ) AS number_of_subcategories, count( DISTINCT p.id ) AS number_of_products FROM categories cLEFT JOIN categories s ON s.parent_id = c.cat_idLEFT JOIN products p ON p.cat_id = c.cat_id AND p.stock >= 1GROUP BY c.cat_nameORDER BY number_of_products ASC\[/code\]At the first glance all goes well, but unfortunately I get total number of all subcategories.Do I miss one more join or what is my problem so far?Here is the code: SQLFiddle