Skip to content

Magento : SQL Directive to Get Products with attributes

here the sql…

select
   
     entity_id,
     sku,
     (
            select value from catalog_product_entity_int as y
            where attribute_id = (select attribute_id  from eav_attribute where attribute_code = 'status' and attribute_id = y.attribute_id ) 
            and entity_id = x.entity_id limit 1
     ) as status,
     (
            select value from catalog_product_entity_varchar as y
            where attribute_id = (select attribute_id  from eav_attribute where attribute_code = 'name' and attribute_id = y.attribute_id ) 
            and entity_id = x.entity_id limit 1
     ) as name,
     
     (
            select value from catalog_product_entity_decimal as y
            where attribute_id = (select attribute_id  from eav_attribute where attribute_code = 'special_price' and attribute_id = y.attribute_id ) 
            and entity_id = x.entity_id limit 1
     ) as special_price,
     (
            select value from catalog_product_entity_decimal as y
            where attribute_id = (select attribute_id  from eav_attribute where attribute_code = 'weight' and attribute_id = y.attribute_id ) 
            and entity_id = x.entity_id limit 1
     ) as weight,
      x.created_at,
      x.updated_at,
      a.attribute_set_name,
      z.category_id,
      (select value from catalog_category_entity_varchar where attribute_id =
 (        select attribute_id from eav_attribute where attribute_code='name' and entity_id = z.category_id limit 1) limit 1  )
      as category_name
      
     
      
     
from catalog_product_entity  as x left join
     eav_attribute_set as a on x.attribute_set_id = a.attribute_set_id left join
    catalog_category_product as z on x.entity_id = z.product_id  


order by x.updated_at desc
limit 100

 

Comments are closed.