Nested Set Model, count items in categories

Question

I've got a Nested Set Model working for my site with items in subcategories and so on. It's working great except for one problem I can't come around.

+---------+-----------------------------+
| item_id | item_name                   |
+---------+-----------------------------+
|       1 | Laptop                      |
|       2 | iPod Classic 80GB           |
|       3 | iPod Classic 160GB          |
+---------+-----------------------------+
+---------+-------------+
| item_id | category_id |
+---------+-------------+
|       1 |           4 |
|       2 |           2 |
|       3 |           2 |
+---------+-------------+
+-------------+--------------------+-----+-----+
| category_id | name               | lft | rgt |
+-------------+--------------------+-----+-----+
|           1 | iPod               |   1 |   6 |
|           2 | Classic            |   2 |   3 |
|           3 | Nano               |   4 |   5 |
|           4 | Computers          |   7 |   8 |
+-------------+--------------------+-----+-----+

Using the following query:

SELECT parent.name, COUNT(product.item_id)
  FROM Category AS node, Category AS parent, Item_Category AS product
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.category_id = product.category_id
  GROUP BY parent.name
  ORDER BY node.lft;

Gives the following output:

+-----------------+------------------------+
| name            | COUNT(product.item_id) |
+-----------------+------------------------+
| iPod            |                      2 |
| Classic         |                      2 |
| Computers       |                      1 |
+-----------------+------------------------+

In other words, all fields that don't have products in them won't be shown. Now to the problem, I want to show them with the COUNT() result = 0. How would my query look to make that happen? :)

Answer 1

Sounds like a task for LEFT OUTER JOIN to me, like so:

SELECT parent.name, COUNT(product.item_id), 
       (select count(*) from Category parent2 
         where parent.lft > parent2.lft
           and parent.rgt < parent2.rgt) as depth
  FROM Category parent
  LEFT OUTER JOIN Category node 
    ON node.lft BETWEEN parent.lft AND parent.rgt
  LEFT OUTER JOIN Item_Category product
    ON node.category_id = product.category_id
 GROUP BY parent.name
 ORDER by node.lft

Thereby, you ensure that all categories are shown. Note that I'm not 100% sure.

EDIT: Added sub-select for depth, give it a try.

EDIT: Removed comma

Cheers, Tom