magento select all the products which have special price

Question

I want to select all the products SKU which have special price through mysql query.

Answer 1

For Magento 1.9. Here 76 is attribute_id for special_price attribute.

select cpe.sku,cpe.type_id, cped.store_id, cped.value from catalog_product_entity_decimal cped
RIGHT OUTER JOIN catalog_product_entity cpe on cpe.entity_id = cped.entity_id 
where cped.attribute_id=76
order by cpe.type_id

Answer 2

here is mysql query (please note that table name can contain prefixes)

select e.sku,pd.store_id, pd.value from catalog_product_entity as e 
inner join  catalog_product_entity_decimal as pd on e.entity_id = pd.entity_id 
inner join eav_attribute as a on pd.attribute_id = a.attribute_id 
where a.attribute_code="special_price"

Answer 3

Below code works perfectly for me.

SELECT e.sku,pd.store_id, pd.value FROM catalog_product_entity AS e

INNER JOIN catalog_product_entity_decimal AS pd ON e.entity_id = pd.entity_id

INNER JOIN eav_attribute AS a ON pd.attribute_id = a.attribute_id

WHERE a.attribute_code="special_price"