Thursday, December 27, 2012

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`


Removing duplicate rows in a table

For removing duplicate rows based on a field in mysql: For example the field 'sku'



ALTER IGNORE TABLE  product ADD UNIQUE KEY idx1(`sku`);



For getting all pro_id s which have duplicate values for a field : For example for the field 'sku'

SELECT A.`pro_id`
FROM product A
INNER JOIN product B ON B.sku = A.sku
WHERE A.`pro_id` <> B.`pro_id`

Now using another sql we can remove all duplicate values using any programing language.