What is the best way to get a list of simple products which don't belong to a configurable?

I have a shop with mostly simple products which belong to a parent/configurable product. But some of the products (eg. jewellery) don't have options so they've been set up as lone simple products.

Ps this is for my own usage only so any solution that provides the right results will do :) thanks


3 Answers 3


Thomas,Magento did not provide this type on function,So without any coding or MySQL Query,you can not get the solution.

Mysql: Magento save the relation between configurable product and it child products at catalog_product_super_link and save it product basic data at catalog_product_entity Table.

enter image description here

Write select query with select type_id=simple simple product and check that product id exit at catalog_product_super_link table product_id. columns.

For getting Simple products that are not associated with a configurable

    SELECT * FROM `jsw_catalog_product_entity` where entity_id 
not in (SELECT product_id FROM `jsw_catalog_product_super_link`) and type_id='simple'
  • 1
    thanks a lot. You've answered a few of my questions now. I owe you a pint ;) Feb 26, 2015 at 17:06
  • Any good idea on how to do something similar in Magento2 using the productRepository? Can it be done with filters?
    – Eugenio
    Oct 18, 2019 at 9:29
  • 1
    The problem with the query above is that you also select normal simple products. Here the one where you only select products that are Not Visible Individually: SELECT COUNT(*) FROM catalog_product_entity WHERE entity_id NOT IN (SELECT product_id FROM catalog_product_super_link) AND type_id='simple' AND entity_id IN (SELECT entity_id FROM `catalog_product_entity_int` WHERE attribute_id = (SELECT attribute_id FROM `eav_attribute` WHERE `attribute_code` LIKE 'visibility') AND `catalog_product_entity_int`.value = 1);
    – Björn
    Dec 21, 2020 at 7:41

Tested in Magento Commerce 2.4.1

This will give you a list of simple products that don't have any parent/child mapping with a configurable product, using a simple left join.

select * from catalog_product_entity e
left join catalog_product_super_link sl on sl.product_id = e.row_id
where sl.product_id is null
and e.type_id = 'simple';


This will give the configurable products with no child assigned.

select *, count(sl.parent_id)
from catalog_product_entity e
left join catalog_product_super_link sl on sl.parent_id = e.row_id
where e.type_id = 'configurable'
group by sl.parent_id
having count(sl.parent_id) = 0;

Note: catalog_product_super_link table keeps the relationship of the parent to child for configurable products and it uses the row_id field to map, NOT the entity_id.

  • This is exactly what I needed. Thank you @Dilhan May 19, 2021 at 1:50

Tested in Adobe Commerce 2.4.3-p1

Created a very simple query to find out the Configurable products Entity Id, SKU, and Type Id for those that have not been associated with any of the Simple Products.

SELECT cpe.entity_id, cpe.sku, cpe.type_id 
    FROM catalog_product_entity AS cpe 
    WHERE cpe.type_id="configurable" AND 
    cpe.row_id NOT IN (
        SELECT parent_id FROM catalog_product_super_link

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.