How to get full list of product with Simple Product SKU Number and Category Name from MySql with Query in Magento?

Question

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.

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