Two tables in MySQL: select from first, sort by second

Question

I have 2 tables. The first one is "product" with "id" and "title" columns. The second is "price" with "productId", "price" and "weight" columns. One product can have several weights with it's own price (Simplified situation.)

I want to select all products sorted (DESC) by their minimal price. How to do this?

Answer 1

SELECT prod.id, prod.title, MIN(price.price) AS minprice
FROM product prod LEFT JOIN price ON price.productId = prod.id
GROUP BY prod.id
ORDER BY minprice DESC

Answer 2

Use a Join; http://www.w3schools.com/Sql/sql_join.asp

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.id=table_name2.productId ORDER BY table_name2.price DESC