SQL Guru needed for MPTT query
I'm trying to use the modified preorder tree traversal method as layed out in the excellent sitepoint article to retrieve a category tree with a count of items below each category.
So far I've come up with this query which is so very nearly there but only returns rows for categories that contain an item.
Can anybody help?
COUNT( items.item_id ) AS item_count
FROM categories AS current, categories AS root, items
WHERE root.category_id != 1
AND ( current.lft BETWEEN root.lft AND root.rgt )
AND ( current.category_id = category_id )
GROUP BY root.category_id
ORDER BY current.lft ASC