select parent.title, parent.level, count(product.title) as sum_products
from category as node, category as parent, product
where node.lft between parent.lft and parent.rght
and node.category_id = product.category_id
group by parent.title
order by node.lft
ลองใช้คำสั่งด้านล่างดูครับ หลักๆคือต้องใช้ LEFT JOIN ครับ คือดึงข้อมูลจากตารางด้านซ้ายมาคำนวน
Code (SQL)
SELECT product.title, COUNT(node.title) AS sum_products
FROM product LEFT JOIN category AS node ON node.category_id = product.category_id
GROUP BY parent.title
select cg.title, count(pd.title) as sum_products
from category as cg
left join product pd on cg.category_id = pd.category_id
group by cg.title
order by cg.title
select cg.title, count(pd.title) as sum_products
from category as cg
left join product pd on cg.category_id = pd.category_id
group by cg.title
order by cg.title
Load balance : Server 05
