CODEWITHSUNDEEP
mysqljoin
John Hall
John Hall

Reputation: 1346

MySQL Retrieve Lowest Value in Multi-table Query

My goal is to retrieve the recorded purchase price for an item on an accepted purchase order.

Purchase_Orders table contains metadata for the order, such as the order number and its status (e.g., 1 for accepted, 0 for declined).

Purchase_Ord_Contents table contains contents records, which are linked via foreign key to the parent purchase order on a shared index order_number)

For example: I have two orders in my database, one has been accepted and the other has been declined. The data is represented as follows:

 =========================================
           PURCHASE_ORDERS TABLE
 =========================================
 id   |   order_number   |   order_status
 -----------------------------------------
  1          PO_100               0
  2          PO_101               1
  3          PO_102               1

===================================================
           PURCHASE_ORD_CONTENTS TABLE
===================================================
id    | order_number | purchase_price |    sku
---------------------------------------------------
   1       PO_100           1.50          APPLE
   2       PO_100           1.50          ORANGE
   3       PO_101           2.00          APPLE
   4       PO_101           2.00          ORANGE
   5       PO_102           1.75          BANANA

The query should return rows 3, 4 and 5, since PO_101 was accepted, whereas PO_100 was declined and row 5 is not only the only record for the given SKU, it was also on an accepted order. I've tried a few different approaches, but I always seem to end up either leaving out parts that were on an unaccepted Purchase Order, or retrieving the wrong order_number for the lowest purchase_price.

Here is what I have thus far (not working properly)

SELECT a.*
FROM   purchase_ord_contents AS a
       JOIN (SELECT sku,
                    MIN(purchase_price) AS min_price
             FROM   purchase_ord_contents
             GROUP  BY sku) AS b
         ON ( a.sku = b.sku
              AND a.purchase_price = b.min_price )
WHERE a.order_number
IN (
   SELECT order_number
   FROM purchase_orders
   WHERE order_status != 0
)

This query successfully returns the records from the purchase_ord_contents table, however it omits records of the lowest purchase_price that were on a Purchase Order with an order_status of 0.

Any guidance would be greatly appreciated, I am not very well versed in "advanced" SQL queries as you have probably determined by now. Thank you for your time and please do not hesitate to ask if I should provide any further information.

Upvotes: 1

Views: 117

Answers (2)

RandomSeed
RandomSeed

Reputation: 29769

This could be what you are looking for:

SELECT sku, purchase_price, order_number
FROM (
    SELECT MIN(purchase_price) AS purchase_price, sku
    FROM purchase_ord_contents
    JOIN purchase_orders USING (order_number)
    WHERE purchase_orders.order_status = 1
    GROUP BY sku
) AS min_sku_price -- this is the lowest sale price for each SKU
JOIN purchase_ord_contents USING (sku, purchase_price) -- gets all orders having sold a SKU at its lowest price
JOIN purchase_orders USING (order_number)
WHERE purchase_orders.order_status = 1

Notice this will return several rows for one given SKU if the lowest price for this SKU was offered in several orders.

Upvotes: 1

Jay
Jay

Reputation: 14471

If I understand correctly I think you want this:

SELECT po.order_number, poc.sku, min(poc.purchase_price)
FROM purchase_orders AS po
 JOIN purchase_ord_contents AS poc ON poc.order_number = po.order_number
 WHERE po.order_status != 0
 GROUP by po.order_number, poc.sku
 order by po.order_number, poc.sku

Upvotes: 0

Related Questions