SQL Query to fetch all child SKUs by using the configurable SKU

Question

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

Answer 1

$parentSku = 'testSku';

SELECT t2.sku FROM 
(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`

Answer 2

select 
  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.

Answer 3

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.

Answer 4

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.