Magento – List of products and category with thesql

categoryMySQLproducts

I want to ask, how to get a list of all products SKU and there categories
For example

+———+—————-+———-+——-+——+
| SKU | category | image | price | msrp |
+———+—————-+———-+——-+——+
| Value 1 | cat1/cat2/cat2 | /123.jpg | 123 | 145 |
+———+—————-+———-+——-+——+

with Mysql query

edit: What I have tried so far :

SELECT e.entity_id, e.sku, a.value FROM catalog_product_super_link b
LEFT JOIN catalog_product_entity AS e ON e.entity_id = b.product_id
LEFT JOIN catalog_category_product AS cp ON cp.product_id =
b.product_id LEFT JOIN catalog_category_entity_varchar AS a ON
a.entity_id = cp.category_id WHERE b.parent_id=368538 group by
b.product_id

Best Answer

Magento saves product & category associations in the catalog_category_product table.

and Sku is saved at product main table catalog_product_entity.Now using query you can fulfill your requirement

Mysql query like

SELECT cat.product_id, GROUP_CONCAT( cat.category_id ) , product.sku
FROM catalog_category_product AS cat, catalog_product_entity AS product
WHERE cat.product_id = product.entity_id
GROUP BY cat.product_id
Related Topic