gistfile1.sql

SELECT `e`.*, 
       `price_index`.`price`, 
       `price_index`.`tax_class_id`, 
       `price_index`.`final_price`, 
       IF(price_index.tier_price IS NOT NULL, Least(price_index.min_price, 
                                              price_index.tier_price), 
       price_index.min_price) AS `minimal_price`, 
       `price_index`.`min_price`, 
       `price_index`.`max_price`, 
       `price_index`.`tier_price`, 
       `cat_index`.`position` AS `cat_index_position` 
FROM   `catalog_product_entity` AS `e` 
       INNER JOIN `catalog_product_index_price` AS `price_index` 
               ON price_index.entity_id = e.entity_id 
                  AND price_index.website_id = '1' 
                  AND price_index.customer_group_id = 0 
       INNER JOIN `catalog_category_product_index` AS `cat_index` 
               ON cat_index.product_id = e.entity_id 
                  AND cat_index.store_id = '1' 
                  AND cat_index.visibility IN( 3, 4 ) 
                  AND cat_index.category_id = '3' 
WHERE  ( `e`.`entity_id` IN(
        SELECT `t1`.`entity_id` 
        FROM   `catalog_product_entity_varchar` AS `t1` 
               LEFT JOIN `catalog_product_entity_varchar` AS `t2` 
                      ON t1.entity_id = t2.entity_id 
                         AND t1.attribute_id = t2.attribute_id 
                         AND t2.store_id = 1 
        WHERE  ( t1.store_id = 0 ) 
               AND ( t1.attribute_id = 976 ) 
               AND ( t1.entity_id = price_index.entity_id ) 
               AND ( IF(t2.value_id > 0, t2.value, t1.value) LIKE '%38915%' ) 

                            ) 
)