Different price requirement depending on the price currency

Viewed 75

I have meta table with the following data:

+----+------------+-----------+------------+
| id | product_id | meta_key  | meta_value |
+----+------------+-----------+------------+
|  1 |          1 | currency  | USD        |
|  2 |          1 | price     | 1100       |
|  3 |          2 | currency  | PLN        |
|  4 |          2 | price     | 1300       |
|  5 |          3 | currency  | USD        |
|  6 |          3 | price     | 1200       |
| 11 |          1 | available | 1          |
| 12 |          2 | available | 1          |
| 13 |          3 | available | 0          |
+----+------------+-----------+------------+

Now I want to fetch product_id if the product is available and the price is above 1000, this can be done with:

SELECT product_id
FROM meta
WHERE meta_key IN ("price", "available") 
GROUP BY product_id 
HAVING 1=1  
    AND SUM(meta_key = "price" AND CAST(meta_value AS DECIMAL(10,2))>=1000) > 0
    AND SUM(meta_key = "available" AND meta_value=1) > 0

Next step is to check the product currency, if I want to fetch products with price above 1000USD, then then product_id=2 shouldn't be returned. The conversion rate for USD/PLN is about 3.63, so 1300PLN is about 357.98USD.

Is there a way to check the product currency and then specify different requirement for the price ? If the currency is USD then the value should be above '1000', if the currency is 'PLN' then the value should be above '3630'.

4 Answers
Related