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)

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.