SQL list all categories and products (SKUs) in category

Question

guys ! How can i list all categories with related products in them (i need the SKUs). Where is the relation table for catalog_category_entity(or varchar) and catalog_product_entity(or varchar). ?

I can use only SQL.. because i don't know php. Thx !

Answer 1

The category product relation table is catalog_category_product. It contains the category id and the product id its related to. Also there is the position column position which determines the order in which the products are fetched.

So to get the product SKU you need do a join with the catalog_product_entity which has the SKU's for the products.

So something like

SELECT
    * 
FROM
    `catalog_category_product` `ccp` 
JOIN
    `catalog_product_entity` as `cpe` 
        ON `cpe`.entity_id = `ccp`.`product_id`

should help you get the SKU's against the product ids. And of course you can replace the SELECT * with only the fields you like to have.

Hope this helps !!

Answer 2

Here is mysql Query to Fetch All products with their categories from magento 1.9

SELECT product_id,product_name,product_path, GROUP_CONCAT(DISTINCT(category_id)) AS category_ids,GROUP_CONCAT(DISTINCT(category_name)) AS category_name,GROUP_CONCAT(DISTINCT(path)) AS category_path FROM (
SELECT DISTINCT B.*,cpev.`value` AS product_path FROM   (SELECT A.*,cv.value AS `category_name`,cc1.value AS path  FROM (
        SELECT
            catalog_product_entity_varchar.entity_id AS product_id,
            catalog_product_entity_varchar.`value` AS product_name,
            catalog_product_entity.sku,
            `at_category_id`.`category_id`
        FROM catalog_product_entity_varchar
        INNER JOIN catalog_product_entity ON catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id
        LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=catalog_product_entity.entity_id)
        WHERE catalog_product_entity_varchar.entity_type_id = (
            SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product' )
        AND catalog_product_entity_varchar.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_product' ))
    ) AS A
    INNER JOIN `catalog_category_entity` AS cat ON cat.`entity_id` = A.category_id
    JOIN `catalog_category_entity_varchar` AS cv ON cat.entity_id = cv.`entity_id`
    JOIN catalog_category_entity_varchar cc1 ON cv.entity_id=cc1.entity_id 
    JOIN `eav_attribute` AS att ON att.`attribute_id` = cv.`attribute_id`
    JOIN `eav_entity_type` AS aty ON att.`entity_type_id` = aty.`entity_type_id`
    WHERE aty.`entity_model` = 'catalog/category' AND att.`attribute_code` = 'name' AND cc1.attribute_id IN (SELECT attribute_id AS attr FROM eav_attribute WHERE attribute_code='url_path')
) AS B
JOIN catalog_product_entity_varchar AS cpev ON cpev.`entity_id` = B.product_id
WHERE cpev.`attribute_id` IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key')) sub_query GROUP BY product_id

Hope this will help you to fetch all products with their categories using mysql query.

Answer 3

Best and easy way to fetch sku's based on the category id from sql.To help anyone looking for an answer that works with Magento 2. x

SELECT product.entity_id as product_id, product.sku FROM catalog_product_entity as product
    INNER JOIN catalog_category_product as cat_prod on product.entity_id = cat_prod.product_id
    WHERE cat_prod.category_id=YOUR Category ID;

Answer 4

To help anyone looking for an answer that works with Magento 2.1.6 (and probably other 2. x versions, but untested), Vikas answer above was great, and all I had to do was change the cv.attribute_id from 41 to 33. This gave me the actual name of a category that I wanted to fetch against the SKU. You can check your database table (catalog_category_entity_varchar) for this info in the attribute_id column.

The final code I used successfully is as follows:

SELECT  e.entity_id As product_id,
        e.sku,
        (Select GROUP_CONCAT(DISTINCT(cv.value)) 
            FROM    catalog_category_entity_varchar AS cv ,
                    catalog_category_product AS at_category_id 
            WHERE   at_category_id.category_id = cv.entity_id AND 
                    (at_category_id.product_id=e.entity_id) AND 
                    cv.attribute_id = 33 and 
                    cv.store_id = 0 ) 
        AS category_name
FROM catalog_product_entity AS e;

This results in a single row for a Product ID, SKU and a single column of comma separated values of all Categories that SKU is linked to.

Answer 5

Here is MySQL Query to fetch all products with their categories by sub-query

SELECT `e`.`entity_id` As `product_id`,`e`.`type_id` As product_type, `e`.`sku`,(Select GROUP_CONCAT(DISTINCT(cv.value)) FROM `catalog_category_entity_varchar` AS cv ,catalog_category_product  AS `at_category_id` where at_category_id.category_id = cv.`row_id`
 AND (at_category_id.`product_id`=e.entity_id) and cv.attribute_id = 41 and cv.store_id = 0 
 ) AS category_name FROM `catalog_product_entity` AS `e`; 

This will help you to fetch all products with their categories.