Results 1 to 14 of 14
Thread: SQL Guru needed for MPTT query
Jan 5, 2010, 14:42 #1
- Join Date
- Apr 2007
- 0 Post(s)
- 0 Thread(s)
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.
SELECT root.name, root.category_id, 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