Help with counting total items using hierarchical data structure

I'm having trouble coming up with a query to count the number of total
items each subcategory with the lowest cost (CPU item).

I'd like to be able to display the total in my pages like so using
PHP.

Automotive (4 items)
- Cars (2 items)
- Trucks (2 items)
- Vans (0 items)
Electronics (6 items)
- Digital Cameras (0 items)
- Portable Audio (3 items)
- Home Theatre & Projectors (0 items)
- TV, DVD & VCR Players (3 items)
- Sound Systems & Components (0 items)


I'm not very good with complex joins so at the moment I am using 4
seperate queries in loops and I know there must be an more efficient
and easier way.

For example, I'd like to count the number of items there are under the
Automotive category such as:

Automotive has 4 items within it (under Cars, Trucks & Vans)
1) 1995 Nissan Pickup
2) 2002 Acura RSX
3) 2005 Honda Civic
4) 2007 Dodge Durango


Also is it better, faster to run the query and store the results to a
seperate table (ie. hourly using cron) and retrieve the total from
that table when needed or is it quick enough to execute that query x
times depending on the number of categories on that page.


These are the 2 tables I have:

Categories table (Using hierarchical data structure)
+--------------+-----+------+------------------------------+
| category_id | lft | rgt | name |
+--------------+-----+------+------------------------------+
| 1 | 1 | 22 | Categories |
| 2 | 2 | 9 | Automotive |
| 3 | 3 | 8 | Cars |
| 4 | 4 | 7 | Trucks |
| 5 | 5 | 6 | Vans |
| 5 | 10 | 21 | Electronics |
| 6 | 11 | 20 | Digital Cameras |
| 7 | 12 | 19 | Portable Audio |
| 8 | 13 | 18 | Home Theatre & Projectors |
| 9 | 14 | 17 | TV, DVD & VCR Players |
| 10 | 15 | 16 | Sound Systems & Components |
+--------------+------+-----+------------------------------+

Items table
+----------+-------------+--------------------------+
| item_id | category_id | title |
+----------+-------------+--------------------------+
| 1 | 4 | 1995 Nissan Pickup |
| 2 | 3 | 2002 Acura RSX |
| 3 | 3 | 2005 Honda Civic |
| 4 | 4 | 2007 Dodge Durango |
| 5 | 7 | iPod Nano |
| 6 | 7 | Creative Zen |
| 7 | 9 | RCA 21" TV |
| 8 | 9 | Sony 36" TV |
| 9 | 9 | Sony 60" Projection TV |
| 10 | 9 | Toshiba 27" TV |
| 11 | 7 | iPod Mini |
+----------+-------------+--------------------------+
.

Relevant Pages

  • Re: Linking data
    ... FROM (AllRecords LEFT JOIN Cars ... You were given instructions on how to build this query in the grid. ... One table has Trucks ...
    (microsoft.public.access.queries)
  • Default value return
    ... I have a query that counts my trailers loaded in a given day for a given ... (SQL below) ... Export Trucks: 0 ... Export Cans: 2 ...
    (microsoft.public.access.queries)
  • Re: Default value return
    ... records for rail included in these results. ... I need the query to return a value of zero for time frames when there aren’t ... Export Trucks and Export Cans? ...
    (microsoft.public.access.queries)
  • Re: Default value return
    ... I need the query to return a default value of 0 for each ... Export Trucks: 0 ... Export Cans: 2 ... Use Nzto convert the nulls to zeroes. ...
    (microsoft.public.access.queries)
  • Re: Selecting Data
    ... The Adapter moves ... > No I am not using "join" in my query. ... > seperate queries to fill each table or can I use one query like above to ... >>> shows the data in the order details table however only with the order ...
    (microsoft.public.dotnet.framework.adonet)