MySQL price & stock comparison

Question

I have a follow-up question to my original post (MySQL Compare prices)

Lets say I have table1 containing the following columns;

EXAMPLE A)

|item   |partnumber  |supplier |stock  |cost 
 CD      11A          West      11      10.11
 CD      11A          East      0       10.00
 USB     BBB          North     1       125.01
 USB     BBB          West      1       101.10

What I have now achieved (by help of @Ali) is for table1 to show the following (which basically shows the supplier with the cheapest price for the product):

EXAMPLE B)

|item   |partnumber  |supplier   |stock |cost 
 CD      11A          East        0      10.00
 USB     BBB          West        1      101.10

This is the code for EXAMPLE B that is working:

SELECT a.name , a.partnumber , a.supplier , a.cost FROM table1 a JOIN (
SELECT name , partnumber , MIN(cost) cost
FROM table1 
GROUP BY name , partnumber ) 
b ON a.name = b.name AND a.cost = b.cost AND a.partnumber = b.partnumber

HOWEVER: IF the stock of the supplier with the cheapest price is 0 but another supplier has more than 0 in stock, eventhough the price isn't the cheapest, how could I achieve that? Following the example West would take the place of East as below:

EXAMPLE C)

 |item   |partnumber |supplier  |stock  |cost 
  CD      11A          West       11     10.11
  USB     BBB          West       1      101.10

To be more clear; always show the supplier with the cheapest item in stock, unless the cheapest supplier doesn't have the item in stock but another one do, in which case show that instead. So, how can I achieve EXAMPLE C)?

Thank you very much!

Answer 1

Have you tried just adding a where condition inside the subquery?

SELECT a.name , a.partnumber , a.supplier , a.cost 
FROM table1 a 
JOIN (
    SELECT name , partnumber , MIN(cost) cost
    FROM table1 
    where stock > 0
    GROUP BY name , partnumber 
    ) b ON a.name = b.name AND a.cost = b.cost AND a.partnumber = b.partnumber