Select query returning only one result per product?

I’m working with a MySQL database, where I have to tables. A product-table with unique product and a price-table that contains Several prices for each product.

Today I use the following query, that will return rows for as many different prices present in price-table for each product:

SELECT product.id as id, price.unit_price as price
FROM product 
   INNER JOIN price 
     ON price.product_id=product.id 
WHERE category_id = 234

How can I change this query to only return a single row for each product with the lowest price (price.unit_price) present in the price-table and is it possible with only a single query?

Answer

This is probably the simplest way:

SELECT product.id as id, min(price.unit_price) as price
FROM product 
   INNER JOIN price 
     ON price.product_id=product.id 
WHERE category_id = 234
group by product.id

Advertisement

Answer