Hi,

Please guide me

How to convert the below query for firebird which is in MYSQL to get the result as below

SELECT parent.name, COUNT(product.name)

FROM nested_category AS node ,

        nested_category AS parent,

        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;

RESULT :

+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+

Reference : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

thank you