I am trying to create a SQL query that will display all the SKUs of a simple product that are related to a configurable product. I will add the configurable SKU via a form field that I want to query.

Magento 1.9.2

4 Answers 4

$parentSku = 'testSku';

(SELECT b.child_id 
FROM `catalog_product_entity` a

LEFT JOIN `catalog_product_relation` b
ON b.`parent_id` = a.`entity_id`

WHERE a.sku = '{$parentSku}') t1

LEFT JOIN catalog_product_entity t2
ON t1.child_id = t2.`entity_id`
  parent.type_id as parent_product_type, parent.sku as parent_sk, 
  child.type_id as child_product_type, child.sku child_sku
from magento.catalog_product_entity as parent
join magento.catalog_product_relation as link 
     on link.parent_id = parent.entity_id
join magento.catalog_product_entity as child 
     on child.entity_id = link.child_id
limit 100

This works better for me.


SELECT entity_id as product_id, sku FROM catalog_product_entity WHERE sku = 'foobarsku'; to get the configurable details by SKU.

After which you could sub-select on the catalog_product_super_link table by product_id and requery the catalog_product_entity table for the additional simple SKUs.

  • Thanks for your reply, but I am looking for the full query, it is the retrieval of the simple products that I am struggling with.
    – chrisb123
    Oct 22, 2015 at 9:23

In the previous answers, join condition seems incorrect. It should be joined with parent product row_id instead of entity_id.

Use below rectified query to get configurable products as well as child products (Magento v2.4):

SELECT e.*, cpr.child_id
FROM catalog_product_entity AS e
LEFT JOIN catalog_product_relation AS cpr ON e.row_id=cpr.parent_id
WHERE e.type_id = 'configurable'

Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.