如何加入两个数据库表并从连接表中返回最低价格

I have two tables, the first table has the product and the second table the prices. The price table could have more than one price per product but I only want to display the lowest. But I keep getting all the prices returned and I'm having trouble figuring out how to do it.

this is what I get returned in my query:

SELECT * FROM products AS pr JOIN prices AS p ON pr.id = p.product_id WHERE pr.live = 1 AND p.live = 1

id product1 name description £100
id product1 name description £300
id product1 name description £200
id product2 name description £50
id product2 name description £80
id product2 name description £60
id product3 name description £222
id product3 name description £234
id product3 name description £235

but I'm after:

id product1 name description £100
id product2 name description £50
id product3 name description £222

Any help would be appreciated

Judicious application of grouping and aggregate functions will achieve the desired results.

SELECT pr.id, pr.title, pr.name, pr.description, min(p.price)
  FROM products AS pr 
    JOIN prices AS p ON pr.id = p.product_id 
  WHERE pr.live = 1 AND p.live = 1
  GROUP BY pr.id

However, if the price column is a text type, it probably won't use the collation you want, and (e.g.) '£100' will be considered less than '£90'.

Group by product id an aggregate min on price

SELECT pr.*, p.m_price
FROM `products` AS pr 
  INNER JOIN (
    SELECT product_id, min(price) AS m_price
    FROM `prices`
    WHERE live = 1
    GROUP BY product_id
  ) AS p 
    ON pr.id = p.product_id 
WHERE pr.live = 1

update: something like this MIN( CAST( SUBSTR( price, 2 ) AS DECIMAL ) could help to get rid of the £ char and convert to number for correct aggregation on price