Hello. I'm just starting with MySQL. I think I know quite a lot comparing to my practice. However I have a trouble with one query. I have two tables in database. Let's say that one of those is table of products. |ID|Product.| |1.|Product1| |2.|Product2| |3.|Product3| The second one is table with different prices of this products. |ID|ProductID|Price| |1 |1........|20...| |2 |1........|30...| |3 |1........|35...| |4 |1........|50...| |5 |2........|10...| |6 |2........|60...| |7 |2........|5....| |8 |3........|2....| |..|.........|.....| The problem is I don't know how to match each product to its lowest price and the second lowest price. For example, the result of query should look like this: |Product |Lowest price|Second lowest price| |Product1|20..........|30.................| |Product2|5...........|10.................| |........|............|...................| I will appreciate any help from you.
you do that by having a query instead of a table in the "from" part of the query and having limit 1, and limit 1,1 to get the lowest and second lowest essentially select products.name, p1.price, p2.price from products inner join (select...) as p1, inner join (select...) as p2
SELECT Product, MIN(price1.Price), MIN(price2.Price) FROM Product LEFT JOIN Price as price1 ON Product.id=price1.ProductID LEFT JOIN Price as price2 ON Product.id=price2.ProductID AND price1.Price <> price2.Price Code (SQL):