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'.