Hello to everyone,
First of all excuse for my english... :)
I'm trying to make work a simple query that extracts from the db all the categories that contains at least one product and show the total number of products contained in each node, ignoring invisible categories (and products inside them).

This is the query:

SELECT
parent.id,
parent.name,
COUNT(products.id) AS numOfProds

FROM CATEGORIES_TABLE AS node

INNER JOIN CATEGORIES_TABLE AS parent
ON node.lft BETWEEN parent.lft AND parent.rgt

INNER JOIN PRODUCTS_TABLE AS products
ON node.id = products.category_id AND node.visible = 1

GROUP BY parent.id
ORDER BY parent.lft

Result must be this:

-- 1 ROOT CAT - 1 object
-------- 2 SUB CAT 1 (in root cat) - 1 object 
-------------- 5 SUB CAT 4 (in sub cat 1) - 1 object 
---------------------- 6 SUB CAT 5 (in sub cat 4) - 1 object 

Instead query returns this:

-- 1 ROOT CAT - 2 objects
------- 2 SUB CAT 1 (in root cat) - 2 objects 
------------- 5 SUB CAT 4 (in sub cat 1) - 1 object 
--------------------- 6 SUB CAT 5 (in sub cat 4) - 1 object 
------------- 3 SUB CAT 2 (in sub cat 1) - 1 object 
--------------------- 4 SUB CAT 3 (in sub cat 2) - 1 object

This is my table structure:

CATEGORIES_TABLE
+----------------------------+---+---+
¦ id ¦ ___________name_____ ¦lft ¦ rgt¦ visible ¦
+----------------------------+---+---+
¦ 1 ¦ ________ROOT CAT____ ¦ 1 ¦ 12 ¦ 1
¦ 2 ¦ _SUB CAT 1 (in root cat) ¦ 2 ¦ 11 ¦ 1
¦ 3 ¦ SUB CAT 2 (in sub cat 1) ¦ 7 ¦ 10 ¦ 0
¦ 4 ¦ SUB CAT 3 (in sub cat 2) ¦ 8 ¦ 9 ¦ 1
¦ 5 ¦ SUB CAT 4 (in sub cat 1) ¦ 3 ¦ 6 ¦ 1
¦ 6 ¦ SUB CAT 5 (in sub cat 4) ¦ 4 ¦ 5 ¦ 1
+----------------------------+---+---+

PRODUCTS_TABLE
+--+----------------------+
¦ id ¦ ______name_______ ¦ category_id ¦
+--+----------------------+
¦ 1 ¦ OBJECT IN SUB CAT 5 ¦ 6
¦ 2 ¦ OBJECT IN SUB CAT 3 ¦ 4
+-------------------------+

Wish can anyone help me!
Thank you all!