0

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

  • i tried this 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 but i get null in my category field – Adel Tunesier Oct 2 '15 at 8:39
1

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
  • i try to get a list of product from catalog_product_super_link with SKU of children products, name of parent, categories of all children in one row and price, – Adel Tunesier Oct 2 '15 at 8:28
  • SKU title category available link image price msrp – Adel Tunesier Oct 2 '15 at 8:31
  • i tried this 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 but i get null in my category field – Adel Tunesier Oct 2 '15 at 8:35
  • ooo, you have chnage your requirement.then pleas the question... – Amit Bera Oct 2 '15 at 8:38
  • i need the name of the categories instead there IDs – Adel Tunesier Oct 2 '15 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.