I got the attribute ids I want to get from the database, they are 426, 74, 439, 92, 81, 425. In addition to those I want the product name, sku and the categories the product is in. The products visibility should be catalog,search only.

This is what I got so far:

cpev.`value` as Name,
cpe.sku as SKU,
catalog_product_entity_varchar cpev
INNER JOIN catalog_product_entity cpe ON cpev.entity_id = cpe.entity_id
INNER JOIN eav_attribute ea on cpev.attribute_id = ea.attribute_id where ea.attribute_id in (426, 74, 439, 92, 81, 425);

Not exactly what I need, but maybe a start. How could I get the data I need from the DB?


1 Answer 1


Please try with the following query.

cpe.sku as SKU,
if(cpev.attribute_id = 71,cpev.`value`,null) as Name,
cv.value as category_name,
max(if(cpev.attribute_id = 426,cpev.`value`,null)) as '426',
max(if(cpev.attribute_id = 74,cpev.`value`,null)) as '74',
max(if(cpev.attribute_id = 439,cpev.`value`,null)) as '439',
max(if(cpev.attribute_id = 92,cpev.`value`,null)) as '92',
max(if(cpev.attribute_id = 81,cpev.`value`,null)) as '81',
max(if(cpev.attribute_id = 425,cpev.`value`,null)) as '425'
FROM `catalog_product_entity` cpe
INNER JOIN `catalog_product_entity_varchar`cpev ON cpev.entity_id = cpe.entity_id
LEFT JOIN  `catalog_category_product` AS at_category ON at_category.product_id = cpe.entity_id
INNER JOIN `catalog_category_entity_varchar` as cv on at_category.category_id = cv.entity_id
GROUP BY cpe.sku
  • Looking good for a start, but its missing the columns for the mentioned attributes (426, 74, 439, 92, 81, 425)
    – Arnie
    Nov 14, 2019 at 13:43
  • so you want all other attributes of product as a separate columns in query output. right? like...need to display attribute values after category_name column? like wise? Nov 14, 2019 at 13:47
  • Yes, exactly, I need a separate column for all the attributes mentioned. The name of the column should be the attribute code.
    – Arnie
    Nov 14, 2019 at 13:48
  • No idea how to solve it?
    – Arnie
    Nov 14, 2019 at 21:10
  • I have edited the query above please check with it and let me know it is working or not. Nov 15, 2019 at 13:58

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.