Magento Enabled Categories with No Products

enabled-categories-with-no-products.sql

# Replace the attribute IDs as needed

SELECT
    cce.entity_id AS "Category ID",
    cce.path as "Category Path",
    ccev.value as "Category Name",
    COUNT(ccp.product_id) as "Number of Products"
FROM catalog_category_entity cce
INNER JOIN catalog_category_entity_varchar ccev
    ON cce.entity_id = ccev.entity_id
INNER JOIN catalog_category_entity_int ccei
    ON cce.entity_id = ccei.entity_id
LEFT OUTER JOIN catalog_category_product ccp
    ON cce.entity_id = ccp.category_id
WHERE ccev.attribute_id = 41
AND ccei.attribute_id = 42
AND ccei.value = 1
GROUP BY cce.entity_id
HAVING COUNT(ccp.product_id) < 1;