CODEWITHSUNDEEP
mysqlsql
Julfikar
Julfikar

Reputation: 1423

MySQL find MIN() from duplicate rows

I have 3 tables like below:

Product Table

PID       CODE
1        a
2        b

Price Table

PrID    PID(ID from Product)   UMID(ID from UOM)  Price
1         1                        1               10
2         1                        2                5
3         2                        1               10

UOM Table

UMID           UM_NAME         UM_RATE
1               BOX              5
2               PC               10

I want to get product with price and that uom which has min UM_RATE. For example, product CODE a has two prices but the prices are different for different uom. What I want to achieve is, 

PID     CODE    PrID    Price     UM_NAME     UM_RATE
1        a       1       10         BOX          5 
2        b       3       10         BOX         5

Because box has min UM_RATE. I have tried the following query,

SELECT product.*, price.*, uom.um_name, MIN(uom.um_rate)
FROM product
LEFT JOIN price ON price.pid = product.pid
LEFT JOIN uom ON price.umid = uom.umid
GROUP BY product.pid

This query gives me the following result,

PID     CODE    PrID    Price     UM_NAME     UM_RATE
1        a       1       10         PC          5 
2        b       3       10         BOX         5

which is wrong. because UM_RATE 5 is belong to UM_NAME box. How can I get the expected result?

Upvotes: 0

Views: 53

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use join and corelated subquery

    select * from (select distinct p.*,u.UMID as uid,
 u.UM_NAME,u.UM_RATE,pr.code from
 price p join uom  u on p.UMID=u.UMID
  join product pr on pr.PID=p.PID
  ) a
  where a.UM_RATE =(select min(UM_RATE) from 
                    (select  p.*,u.UMID as uid,
 u.UM_NAME,u.UM_RATE,pr.code from
 price p join uom  u on p.UMID=u.UMID
  join product pr on pr.PID=p.PID) t where t.code=a.code

      )


prid    PID     UMID    price   uid     UM_NAME     UM_RATE     code
1       1       1       10      1       Box           5          a
3       2       1       10      1       Box           5          b

Upvotes: 1

Related Questions