I want to pull data from Mysql with query in magento, I need data in three columns SKU Number, Category name and product Name etc.

1 Answer 1


I think the attribute_id values used in this query are default values, but you can check for the correct ones with the following two queries:

For product-name:

select attribute_id from eav_attribute 
where entity_type_id = 4 and attribute_code = 'name';

For category-name:

select attribute_id from eav_attribute 
where entity_type_id = 3 and attribute_code = 'name';

These two values are used in the last line of the Final Query:

select cpe.sku 'Sku', ccev.value 'Category Name', cpev.value 'Product Name'
from catalog_product_entity cpe
join catalog_product_entity_varchar cpev
on cpe.entity_id = cpev.entity_id
join catalog_category_product ccp
on ccp.product_id = cpe.entity_id
join catalog_category_entity_varchar ccev
on ccev.entity_id = ccp.category_id
where cpev.attribute_id = 71 -- THIS IS THE PRODUCT NAME
and ccev.attribute_id = 41; -- THIS IS THE CATEGORY NAME

Your Answer

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

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