Geek asks and answers

Show parent counter on grand child

I have two tables

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

Here is the detailed information. A
category can have several id subcategories. Parent id 0 means it is a category and parent id! = 0 means it is a subcategory. Now each product is associated with a subcategory. I need to display category names and the total number of subcategories related to each category. selling 0 means the product is being sold and 1 means it is necessary. Now I need it. 1. Define all categories and the number of subcategories related to each category in which goods belonging to the category are sold.

+1
source share
2 answers

Well, I 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 works well.

0

, . , .

0

More articles:


All Articles