Query to Get All Simple Products Not Part of a Complex Product in Magento

collection;product

Exactly as the title suggests, how can i find all simple products in the system that are not part of complex products such as configurable?

I dont mind if this is done directly in the db or through some collection load but i need only the list of product ids that fit this criteria.

Best Answer

Try this query:

SELECT
    entity_id
FROM
    catalog_product_entity
WHERE
    type_id = 'simple'
AND(
    entity_id NOT IN(
        SELECT DISTINCT
            (product_id)
        FROM
            `catalog_product_super_link` /* assocciated product ids of configurebles */
        UNION
            SELECT DISTINCT
                (linked_product_id)
            FROM
                `catalog_product_link` /* assocciated product ids of grouped */
    )
)
Related Topic