MySQL Query filtered by attribute_set_id

Question

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

Answer 1

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')

Answer 2

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'