Self joining for listing categories, parent categories and its product counts

Self joining for listing categories, parent categories and its product counts

SELECT tab1.`cat_id`, tab1.`cat_name`, tab2.`cat_name` AS parent_name, tab3.pro_count  FROM `fha_category` tab1 LEFT JOIN `fha_category` tab2 ON tab2.`cat_id` = tab1.`cat_parent_id` LEFT JOIN (SELECT COUNT(`pro_id`) AS pro_count, `cat_id` FROM `fha_product_to_category` WHERE 1 GROUP BY `cat_id`) tab3 ON tab3.`cat_id` = tab1.`cat_id`