显示基于孙代的父母计数 [英] Display Parent Count base on the grand-child

问题描述

我有两个表

Table1 : Categories , 
Columns : id , parent_id , name 
Table2 : products ,   
Columns : id , product_name , category_id , subcategory_id , sale_wanted

这是细节
一个类别可以具有多个子类别ID.父代ID 0表示它是一个类别，父代ID！= 0表示它是一个子类别.现在，每个产品都与一个子类别相关.我需要显示类别的名称以及与每个类别相关的子类别总数. 销售0表示要出售的产品，1表示需要. 现在我需要这个.
1.显示所有类别和与每个类别相关的产品在其中销售的每个类别的子类别计数.

Here is the detail
A category can have multiple subcategories id. Parent id 0 means it s a category and parent id != 0 means it is a subcategory. Now each product is related to a subcategory. I need to display the names of category and total subcategories count related to each category. sale 0 means the product is for sale and 1 means it is required. Now i need this.
1.Display all categories and count of subcategories related to each category where the products related to category are for sale.

推荐答案

好，我已经找到了这种解决方案

Well i have found this solution

select 
    dc.category_id,
    dc.name ,
    count(ldc.name) as total
from default_category as dc
inner join default_category as ldc on ldc.parent_id= dc.category_id 
inner join(select * from default_products where sale_wanted = 1) as dp on dp.subcategory_id = ldc.category_id
where dc.parent_id = 0
group by dc.category_id 

这很好.

这篇关于显示基于孙代的父母计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！