1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Problem with query

Discussion in 'MySQL' started by Lokisame, Oct 3, 2018.

0
  1. #1
    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.
     
    Lokisame, Oct 3, 2018 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    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
     
    sarahk, Oct 3, 2018 IP
  3. SaleSmith

    SaleSmith Peon

    Messages:
    24
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3 
    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):
     
    SaleSmith, Oct 6, 2018 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    @SaleSmith's example looks good, you'll need to get some sorting into those joins though
     
    sarahk, Oct 6, 2018 IP