Get product Category from Parent Product using SQL query

Question

I have a collection of associated products of a configurable product. I want to get category for this products. The categories are assigned to their parent product(Configurable Product) and not to the simple products which I have.

I am using below code to add category_id to my collection

$_productCollection->getSelect()->joinLeft('catalog_category_product',
    'e.entity_id = catalog_category_product.product_id',
    array('product_category'=>'category_id'));

It works but for associated products, it shows NULL, I want to check if there is Null value for category_id then use category_id of its Parent Product.

Does anyone have any idea how to get categories from parent product using an SQL query?

Answer 1

You can use below query to get parent product category id if there is no category assigned to associated products.

$_resultCollection->getSelect()->joinLeft('catalog_category_product',
'e.entity_id = catalog_category_product.product_id',
array('product_category'=>'IFNULL(catalog_category_product.category_id,
(SELECT `catalog_category_product`.`category_id` FROM `catalog_product_super_link` 
LEFT JOIN `catalog_category_product` ON `catalog_category_product`.`product_id` = `catalog_product_super_link`.`parent_id` 
where `catalog_product_super_link`.`product_id` = `e`.`entity_id` 
GROUP BY `catalog_product_super_link`.`product_id`))'));