Get the count of the number of items under the children categories of a given category in MySQL

Question

Say we have this tables

t1
--------------------------------
category_id | name | lft | rgt
--------------------------------
1             cat1   1     8
2             cat2   2     3
3             cat3   4     7
4             cat4   5     6

t2
-------------------------------
item_id | category_id
--------------------------------
 1          1
 2          2
 3          3
 4          4

Is there a way in MySQL to get the number of items a category has(including those items that belong to its children)? Something like this...

-------------------------------
category_id | item_count
--------------------------------
 1              4
 2              1
 3              2
 4              1

I have trouble connecting the query that gets the category_ids and the query that gets the count of the children categories.

SELECT category_id FROM t1 WHERE <some conditions here>

SELECT 
  COUNT(*) AS item_count,
  category_id
FROM
  t2
WHERE
  t2.category_id IN 
  (
 SELECT  
      node.category_id
 FROM 
  t1 AS node,
     t1 AS parent
    WHERE
  node.lft BETWEEN parent.lft AND parent.rgt
     AND parent.category_id = 5 <---- How do I pass the category ids of the first query
                                      here?
  )        

Answer 1

SELECT a.category_id, COUNT(a.item_id) as itemcount 
FROM itemTable a 
GROUP BY a.category_id;

All you need to do is to find out how you can add the ones that don't have any items to this.

EDIT: Okay, so the problem is getting a value into a subselect... I'm not an expert at SQL, but I suggest creating a view.

SELECT category_id FROM t1 WHERE <some conditions here>
CREATE VIEW temp AS
 SELECT  
  node.category_id as nc_id, parent.category_id as pc_id
 FROM 
  t1 AS node,
  t1 AS parent
 WHERE
  node.lft BETWEEN parent.lft AND parent.rgt;

VIEW temp
nc_id | pc_id
1   1
2   1
3   1
4   1
2   2
3   2
4   3


t2
itemid| catid
1   1
2   2
3   3
4   4

CREATE VIEW temp2 AS
   SELECT 
     *
   FROM
     t2, temp
   WHERE t2.category_id = temp.nc_id OR t2.category_id = temp.pc_id

cat_id| itemid |nc_id | pc_id
1   1   1   1
1   1   2   1
1   1   3   1
1   1   4   1
2   2   2   1
2   2   2   2
2   2   3   2
3   3   3   1
3   3   3   2
3   3   4   3
4   4   4   1
4   4   4   3

CREATE VIEW temp3 AS
SELECT cat_id, itemid, nc_id, pc_id
FROM temp2
GROUP BY item_id, nc_id;

temp3:
cat_id| itemid |nc_id | pc_id
1   1   1   1
1   1   2   1
1   1   3   1
1   1   4   1
2   2   2   1
2   2   3   2
3   3   3   1
3   3   4   3
4   4   4   1

SELECT count(itemid) AS itemcount, cat_id
FROM temp3
GROUP BY cat_id;

itemcount      |cat_id
4       1
2       2
2       3
1       4

DROP VIEW temp3;
DROP VIEW temp2;
DROP VIEW temp;

And thus concludes my answer. I hope it works, and I also hope you can clean my mess up (in terms of optimizing what I wrote)