Ttdfjt

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

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.

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