Query to retrieve all product ids and the category they belong to in Magento.

Magent_Product_Category_Query.sql

SELECT `e`.entity_id, `at_category_id`.`category_id` 
FROM `catalog_product_entity` AS `e` 
LEFT JOIN `catalog_category_product` AS `at_category_id`
ON (at_category_id.`product_id`=e.entity_id)

products in multiple categories minus the default category

SELECT entity_id, REPLACE(category_ids, '2,', '') as category_id FROM (
    SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM (
        SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
            SELECT `e`.entity_id, `at_category_id`.`category_id` 
            FROM `catalog_product_entity` AS `e` 
            LEFT JOIN `catalog_category_product` AS `at_category_id`
            ON (at_category_id.`product_id`=e.entity_id)
        ) sub_query
        GROUP BY entity_id
    ) middle_query
    HAVING category_cnt > 2
) main

@tegansnyder

These are awesome. Is it possible to pull in the actual category name from the catalog_category_entity_varchar table?

@tegansnyder i have the same question, how i can put category names in this sql?
SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM ( SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM ( SELECT e.entity_id, at_category_id.category_id FROMcatalog_product_entityASe LEFT JOINcatalog_category_productASat_category_id ON (at_category_id.product_id=e.entity_id) ) sub_query GROUP BY entity_id ) final_query HAVING category_cnt > 2

Looking at FrontEnd Data you notice 2005 market size for Computers and Peripherals is smaller than Monitors. That is wrong as Monitor is a part of Peripheral. Where is the error in project setup if we assume that all calculations are done exactly as they are set by rules in the available tables?

Please solve this question.

Possible to query all in stock products by category id?

How to get best selling products list based on category-wise?

ChatGTP:

To create an SQL query that retrieves all category names for a specific product entity_id in a Magento shop database, you'll need to understand the database schema for Magento. Magento uses an EAV (Entity Attribute Value) model for storing data, which can make querying it directly a bit complex. However, for fetching category names for a specific product, we'll primarily be concerned with a few key tables:

1. catalog_category_entity_varchar: This table stores category attributes, including the name of the category.
2. catalog_category_product: This table links products to categories.
3. catalog_product_entity: This table stores product information.

Given this, the SQL query to fetch all category names for a specific product entity_id would involve joining these tables. The precise query might vary slightly depending on your Magento version (e.g., Magento 1 vs Magento 2), but the following is a general approach for Magento 2:

SELECT ccev.value AS category_name
FROM catalog_category_entity_varchar AS ccev
JOIN catalog_category_entity AS cce ON ccev.entity_id = cce.entity_id
JOIN catalog_category_product AS ccp ON ccp.category_id = cce.entity_id
JOIN catalog_product_entity AS cpe ON ccp.product_id = cpe.entity_id
WHERE cpe.entity_id = :productEntityId
AND ccev.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'))
AND ccev.store_id = 0;

In this query:

• Replace :productEntityId with the specific product entity_id you're inquiring about.
• This query assumes you're looking for category names in the default store view (store_id = 0). You might need to adjust store_id based on your setup.
• The query dynamically retrieves the attribute_id for the category name, which allows it to be more adaptable to different Magento setups.

Remember, the exact structure of your Magento database might vary, especially if customizations or extensions are involved. It's a good idea to check your database schema to ensure these table and column names align with your setup. Also, running complex queries directly against your Magento database should be done with caution, especially on a live system, as it can impact performance.