Get the special price of the product using query in Magento

Question

I want to create a view of Magento products. The following query will do that:

Note: Magento's default records used to create it.

SELECT
   P.entity_id as uid,
   P.created_at as cdate, # Created to updated date?
   PN.value as name,
   C.attribute_set_name as category,
   PR.value as price,
   'INR' as price_unit,
   concat('http://www.mydemo.com/media/catalog/product/', U.value) as pic_url,
   D.value as description,
   concat('http://www.mydemo.com/', PU.value) as product_url

FROM catalog_product_entity as P
   LEFT OUTER JOIN catalog_product_entity_varchar as PN
       ON P.entity_id = PN.entity_id
           AND PN.attribute_id = 71 # 71 Product name
   LEFT OUTER JOIN eav_attribute_set C
       ON C.attribute_set_id = P.attribute_set_id
   LEFT OUTER JOIN catalog_product_entity_decimal PR
       ON PR.entity_id = P.entity_id
       AND PR.attribute_id = 75 # Price
   LEFT OUTER JOIN catalog_product_entity_media_gallery as U
       ON P.entity_id = U.entity_id
       JOIN catalog_product_entity_media_gallery_value as V
           ON U.value_id = V.value_id
               AND V.position = 2
   LEFT OUTER JOIN catalog_product_entity_text as D
       ON P.entity_id = D.entity_id
           AND D.attribute_id = 72
   LEFT OUTER JOIN catalog_product_entity_varchar as PU
       ON P.entity_id = PU.entity_id
       AND PU.attribute_id = 98
       AND PU.store_id = 1
WHERE P.entity_id in ('402', '403', '404', '413')
ORDER BY P.entity_id

Here, it will show the products with its price. For the product id 403 it shows the price as 175.00 but it has a special price 140. What I want to show is if special price is there it show that otherwise its actual price.

Currently:

uid  price
402  190.00
403  175.00 # this is wrong because it has spacial price is 140 
404  160.00
413  125.00

Expected result:

uid  price
402  190.00
403  140.00 
404  160.00
413  125.00

If I pass PR.attribute_id = 75, it gives price if PR.attribute_id = 76 it gives special price. How can we show both values in the same result. How should I modify the query to achieve this?

UPDATE:

The query is updated as below to get the value as expected:

SELECT
  #CP.special_from_date,
  #CP.special_to_date,
   P.entity_id as uid,
   P.created_at as cdate, # Created to updated date?
   PN.value as name,
   C.attribute_set_name as category,
   #PR.value as price,
   #PRN.value as normal_price,
   #PRS.value as special_price,
   coalesce(PRS.value, PRN.value) as price,
   'INR' as price_unit,
   concat('http://www.mydemo.com/media/catalog/product/', U.value) as pic_url,
   D.value as description,
   concat('http://www.mydemo.com/', PU.value) as product_url

FROM catalog_product_entity as P
   LEFT OUTER JOIN catalog_product_entity_varchar as PN
       ON P.entity_id = PN.entity_id
           AND PN.attribute_id = 71 # 71 Product name
   LEFT OUTER JOIN eav_attribute_set C
       ON C.attribute_set_id = P.attribute_set_id

   INNER JOIN catalog_product_entity_decimal PRN
       ON PRN.entity_id = P.entity_id
       AND PRN.attribute_id = 75 # Price
   LEFT JOIN catalog_product_entity_decimal PRS
       ON PRS.entity_id = P.entity_id
       AND PRS.attribute_id = 76 # Special Price

   LEFT JOIN catalog_product_flat_1 as CP
        ON CP.entity_id = P.entity_id
          AND CURRENT_DATE() BETWEEN CP.special_from_date and CP.special_to_date

   LEFT OUTER JOIN catalog_product_entity_media_gallery as U
       ON P.entity_id = U.entity_id
       JOIN catalog_product_entity_media_gallery_value as V
           ON U.value_id = V.value_id
               AND V.position = 2
   LEFT OUTER JOIN catalog_product_entity_text as D
       ON P.entity_id = D.entity_id
           AND D.attribute_id = 72
   LEFT OUTER JOIN catalog_product_entity_varchar as PU
       ON P.entity_id = PU.entity_id
       AND PU.attribute_id = 98
       AND PU.store_id = 1

Another thread which helped me to fix it: https://stackoverflow.com/questions/31770234/how-the-write-the-query-to-get-the-special-price-if-available

Answer 1

You could join the query to the decimal table twice, picking up price once and special_price the second time, then use an if statement in the SELECT part of the query to return the right one. The answer you get may still be wrong though, as there's another two fields, special_to_date and special_from_date that you'll have to check to make sure the special price is valid.

The "proper" (more accurate but slower) way of doing your query there would be to use a ProductCollection to query for what you want at let Magento worry about how it's data structures work.

Answer 2

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