List of products and category with mysql

Question

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

Answer 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

Answer 2

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];