CODEWITHSUNDEEP
sqlmysql
Simon Perepelitsa
Simon Perepelitsa

Reputation: 20639

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

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?

Upvotes: 0

Views: 586

Answers (2)

Tim
Tim

Reputation: 9489

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

Upvotes: 1

Tor Valamo
Tor Valamo

Reputation: 33749

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

Upvotes: 4

Related Questions