0

I'm missing one piece to my puzzle here.. I want to get entity_id, product name, and filter it by the products being configurable, websiteid being 2 and by excluding a specific attribute set name.

Problem: what table to join to, in order to exclude attribute_set_id=22(in my case)

SELECT e.entity_id AS product_id, var.value AS product_name,var.store_id,e.type_id,c.website_id
FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var,catalog_product_website c
WHERE
   e.entity_type_id = eav.entity_type_id
   AND eav.attribute_code = 'name'
   AND eav.attribute_id = var.attribute_id
   AND var.entity_id = e.entity_id
   AND var.entity_id=c.product_id
   AND e.type_id='configurable'
   AND c.website_id=2
0

Try this.

   SELECT DISTINCT `e`.*, `at_store_id`.`store_id` 
 FROM `catalog_product_entity` AS `e` 
 LEFT JOIN `catalog_category_product_index` AS `at_store_id` 
 ON (at_store_id.`product_id`=e.entity_id) AND (at_store_id.store_id = 1) 
 INNER JOIN `catalog_product_website` AS `product_website` 
 ON product_website.product_id = e.entity_id AND product_website.website_id = '1' 
 WHERE (`e`.`type_id` = 'configurable') AND (`e`.`attribute_set_id` != '4')
1
  • Although this did not give me the right product name, i realized my query only needed e.attribute_set_id !='22'. Thank you @sukehshini – iPetey Feb 26 '15 at 13:42
0

Thanks to Sukeshini, i actually realized all i was missing was and e.attribute_set_id!='22'. This will give you the entity_id and the product name but will allow you to pick which websiteID, configurable or simple products, as well as excluding an attribute set by ID.

SELECT e.entity_id, var.value AS name
FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var,catalog_product_website c
WHERE
   e.entity_type_id = eav.entity_type_id
   AND eav.attribute_code = 'name'
   AND eav.attribute_id = var.attribute_id
   AND var.entity_id = e.entity_id
   AND var.entity_id=c.product_id
   AND e.type_id='configurable'
   AND c.website_id=2 and e.attribute_set_id!='22'

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.