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
1
  • 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 Oct 2, 2015 at 8:39

2 Answers 2

0

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
6
  • 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, Oct 2, 2015 at 8:28
  • SKU title category available link image price msrp Oct 2, 2015 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 Oct 2, 2015 at 8:35
  • ooo, you have chnage your requirement.then pleas the question...
    – Amit Bera
    Oct 2, 2015 at 8:38
  • i need the name of the categories instead there IDs Oct 2, 2015 at 8:41
0

Your SQL query will be like this:

SELECT product.entity_id as product_id, product.sk [Other attributes here]
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 IN [YOUR Category IDs];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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