MySQL Check if a value is within the range of values derived from a subquery

Question

I need help with a query, I have a table ,here is an example

Item Code   Qty   Price   Supplier
1234          1      20    A
1234          3      15    B
1234          6      2     C
4321          2      8     D
4321          7      1     A
4321          9      5     G
5432          8      10    E
5432          3      2     F
5467          5      9     H
5467          5      7     K

I have a subquery which contains distinct Item code, max(price) and 75%of max(price),this is the result.

Item Code   Max value   Min Value
1234         20          15
4321         8           6
5432        10           7.5
5467         9           6.75

and I need pull only those rows from first table if the price falls within the range of second table for the particular Item Code. Can anyone help?

Answer 1

I will get first the max price per itemCode and join to the main table using itemCode and chwck that the price is between 75% of max and max price per item.

Select tbl.*
From myTable tbl
Join (
Select item_code,
         max(price) as mx,
         Round(max(price)*.75,0) as mx75
From myTable
Group by item_code) tab
On tbl.item_code = tab.item_code
And tbl.price between tab.mx75 and tab.mx
Order by 1, 4