CODEWITHSUNDEEP
mysqlsql
pocketrocket
pocketrocket

Reputation: 23

Get the minimum price of each product type with currency conversion

I would like to select the cheapest (including the cost of shipping, with prices converted to the local currency) of each of the product types. cheapest = (product.price + product.shipping) * seller.to_aud

My database has tables as follows:

PRODUCTS                                           SELLERS
-----------------------------------------------    --------------------------
| id | type_id | seller_id | price | shipping |    | id | currency | to_aud |
-----------------------------------------------    --------------------------
| 1  | 1       | 1         | 10    | 5        |    | 1  | usd      | 0.9875 |
-----------------------------------------------    --------------------------
| 2  | 1       | 2         | 10    | 2        |    | 2  | gbp      | 1.6000 |
-----------------------------------------------    --------------------------
| 3  | 1       | 1         | 13    | 0        |
-----------------------------------------------
| 4  | 2       | 1         | 8     | 4        |
-----------------------------------------------
| 5  | 2       | 2         | 8     | 2        |
-----------------------------------------------
| 6  | 2       | 2         | 15    | 0        |
-----------------------------------------------

If all of the sellers used a single currency and I wasn't adding the cost of shipping, I could get the result I wanted with:

SELECT a.id, a.price
FROM
(
 SELECT type_id, min(price) as minprice
 FROM products
 GROUP BY type_id
) AS b INNER JOIN products as a on a.type_id = b.type_id and a.price = b.minprice
ORDER BY price

But I cannot figure out where to go from here. Any help would greatly appreciated.

Upvotes: 2

Views: 867

Answers (1)

Tanvir Kaiser
Tanvir Kaiser

Reputation: 253

    SELECT a.id, a.price*ISNULL(s.to_aud,1) as minprice
FROM
(
 SELECT type_id, min((price+shipping)) as minprice
 FROM products
 GROUP BY type_id
) AS b INNER JOIN products as a on a.type_id = b.type_id and (a.price+a.shipping) = b.minprice
Inner join sellers s on s.id = a.seller_id
ORDER BY price

Upvotes: 1

Related Questions