CODEWITHSUNDEEP
phpmysqlsqlrelational-database
Yourguide
Yourguide

Reputation: 109

Getting whole row of MIN value in complex MySQL query

I have found several posts that discuss getting the whole row of a table with a specific MIN() value, but I am not understanding how to add that into my somewhat complex query already.

I am using this to create a view so I have a table of items and their lowest active cost. Other processes are already in place to use this view that contains 4 columns: ItemNumber - PL_IPRecNbr Cost - Cost Description of Price Sheet - PH_VndrDocumentID PriceSheet Record # - PH_RecNbr

Here is my MySQL Query:

SELECT `pl`.`PL_IPRecNbr` AS `PL_IPRecNbr`,
IF (min(`pl`.`PL_ATRLandedCostEach`) = 0, min(`pl`.`PL_ATRCostEach`), 
min(`pl`.`PL_ATRLandedCostEach`)) AS `Cost`,
`ph`.`PH_VndrDocumentID` AS `PH_VndrDocumentID`,
`ph`.`PH_RecNbr` AS `PH_RecNbr`
FROM `pl`
JOIN `ph` ON 
`pl`.`PL_PHRecNbr` = `ph`.`PH_RecNbr`
WHERE isnull(`ph`.`PH_ReplacedByPH_RecNbr`)
AND `ph`.`PH_ExcludeSheetFromAPS` = 'N'
AND (`pl`.`PL_PurchaseQty` > `pl`.`PL_SoldQty` OR `pl`.`PL_PurchaseQty` = 0)
AND cast(now() AS date) BETWEEN `ph`.`PH_PricesStartDate`
AND `ph`.`PH_PricesEndDate`
GROUP BY `pl`.`PL_IPRecNbr`

The problem is that the value I get for "Cost" is correct, but the values I get for "PH_VndrDocumentID" and "PH_RecNbr" are not correct. I need them to be the values from the same row as the cost.

I have tried many attempts and searched many answers but cannot wrap my head around how to achieve the results I need.

Table Descriptions for reference: 

PH is a table of "Price Sheet Headers"
 - Also called just Price Sheets.
 - They are "Active when they fall into a start and end date range of today
 - There are multiple price sheets active for each item, I need the lowest one.
 - There are sometimes limits on how many items are available thats why we check to see if the purchased qty is greater than the Sold Qty. 
 - If there is no limit on items the PurchaseQty shows a 0.



PL is a tables of Prices belonging to each "PH".
 - Each line is a Price for an item 
 - The price is either in one of two columns either PL_ATRLandedCostEach or PL_ATRCostEach, if Landed is 0 then its in the other column, that why the IF.
as you can see I am trying to determine the lowest price and corresponding Price Sheet (PHRecNbr) using criteria that finds only active price sheets based on the date and those other factors.

Any discussion or pointers would be helpful. Thank you in advance!

Upvotes: 0

Views: 53

Answers (1)

Brando McLeod
Brando McLeod

Reputation: 16

Will your pl always have a ph.PH_RecNbr match of PL_PHRecNbr? If so, you can kill the join and force the correct PH_VndrDocumentID PH_RecNbr (getting rid of any ambiguity caused by a JOIN):

SELECT
  pl.PL_IPRecNbr           AS PL_IPRecNbr,
  IF (
    min( `pl`.`PL_ATRLandedCostEach` ) = 0,
    min( `pl`.`PL_ATRCostEach` ),
    min( `pl`.`PL_ATRLandedCostEach` )
  )                        AS `Cost`,
  `ph`.`PH_VndrDocumentID` AS `PH_VndrDocumentID`,
  `ph`.`PH_RecNbr`         AS `PH_RecNbr`
FROM
  pl,
  ph
WHERE
  pl.PL_PHRecNbr = ph.PH_RecNbr     AND 
  ph.PH_ReplacedByPH_RecNbr IS NULL AND
  ph.PH_ExcludeSheetFromAPS = 'N'   AND
  pl.PL_PurchaseQty > pl.PL_SoldQty AND
  pl.PL_PurchaseQty = 0             AND 
  cast(now() AS date) BETWEEN ph.PH_PricesStartDate AND ph.PH_PricesEndDate
GROUP BY
  pl.PL_IPRecNbr

This will force each row returned to be based on the single pl.PL_IPRecNbr item (assuming there is only one of these per row).

Upvotes: 0

Related Questions