Find lowest value for each row of db - sql

Question

Ive got a table of price info as the image below, im trying to query the db to give me back the 'item' and the cheapest supplier value.

So for the table below it would be

• bike, Supplier 3 - 120.43
• shoe, Supplier 2 - 19.99
• ball, Supplier 1 - 5.50

Is this possible to do with sql querys, or would you need to output all the values and then filter them with php (or language of choice) ?

Answer 1

Change your schema.

Table suppliers

supplier_id    supplier_name
1              Supplier 1
2              Supplier 2
3              Supplier 3

Table products

product_id     product_name
1              Bike
2              Shoe
3              Ball

Table prices

product_id     supplier_id     price
1              1               125.65
1              2               132.01
1              3               120.43
2              1               23.99
2              2               19.99
2              3               25.96
3              1               5.50
3              2               8.65
3              3               6.50

Your query

SELECT product_name, supplier_name, price
FROM prices
INNER JOIN products ON products.product_id = prices.product_id
INNER JOIN suppliers ON suppliers.supplier_id = prices.supplier_id
GROUP BY prices.product_id
HAVING price = MIN(prices.price)

Answer 2

Use LEAST()

select item, least(supplier1, supplier2, supplier3)
from your_table