MySQL joins the next lower value from another table

Question

I have two tables orders

id  article amount
1   1       1               
2   2       50              

and prices

id  article min_amount  price
1   1       1           42.99   
2   2       1           5.06    
3   2       5           4.55    
4   2       10          4.3     
5   2       25          4.05    
6   2       100         2.66    

The tables pricescontain article IDs and the minimum amount you would have to buy in order to receive a massive discount (which will change the order price). I would like to join pricesin ordersso that the result looks like this:

id  article amount  price
1   1       1       42.99
2   2       50      4.05

Order ID 2 is above the minimum (25) to get an article for € 4.05, but still below 100, at which you will get a big discount, so the request would have to choose the next lower value.

I have tried this request so far

SELECT 
    orders.id AS id,
    orders.article,
    orders.amount,
    prices.price,
    (orders.amount - prices.min_amount) AS discount_diff
FROM orders
LEFT JOIN prices ON (prices.article = orders.article) AND (prices.min_amount <= orders.amount)

which gives this result

id  article amount  price   discount_diff
1   1       1       42.99   0
2   2       50      5.06    49
2   2       50      4.55    45
2   2       50      4.3     40
2   2       50      4.05    25 

This example can be found on the "js" script: http://sqlfiddle.com/#!9/1b2bf/8

Answer 1

:

SELECT orders.id AS id,
       orders.article,
       orders.amount,
       prices.price
  FROM orders
         INNER JOIN prices ON ( prices.article = orders.article
                                and prices.min_amount <= orders.amount)
         INNER JOIN ( SELECT orders.article,
                             orders.amount,
                             min(prices.price) minprince
                        FROM orders
                               INNER JOIN prices ON (prices.article = orders.article
                                                   AND prices.min_amount <= orders.amount)
                       GROUP BY orders.article,
                                orders.amount) b
                ON (     prices.article = b.article
                    AND orders.amount = b.amount
                    AND prices.price = b.minprince) 

: http://sqlfiddle.com/#!9/1b2bf/27