CODEWITHSUNDEEP
mysql
frianH
frianH

Reputation: 7563

INNER JOIN table to get MIN value from duplicate id records

Trying to join the tables to get the MIN value of each record, the following table info:

*tblProduct

prodId  title       description
-------------------------------
1001    title001    desc001
1002    title002    desc002
1003    title003    desc003

*tblPriceVariant

prodId  minOrder    pricePcs
----------------------------
1001    10          10000
1001    20          7500
1001    30          5000
1002    100         50000
1002    200         40000
1003    10          2500

The query I tried, but the results of the both not what I want:

SELECT tblProduct.prodId, tblProduct.title, tblPriceVariant.pricePcs
FROM tblProduct INNER JOIN tblPriceVariant ON tblProduct.prodId=tblPriceVariant.prodId

And:

SELECT tblProduct.prodId, tblProduct.title, MIN(tblPriceVariant.pricePcs)
FROM tblProduct INNER JOIN tblPriceVariant ON tblProduct.prodId=tblPriceVariant.prodId

Expected result:

prodId  title       pricePcs
----------------------------
1001    title001    5000
1002    title002    40000
1003    title003    2500

How can I fix it?

Upvotes: 1

Views: 551

Answers (3)

fthiella
fthiella

Reputation: 49049

Your first query is almost correct, but you forgot the group by clause:

select
  tblProduct.prodId,
  tblProduct.title,
  min(tblPriceVariant.pricePcs)
from
  tblProduct inner join tblPriceVariant
  on tblProduct.prodId=tblPriceVariant.prodId
group by
  tblProduct.prodId,
  tblProduct.title

Upvotes: 1

Akina
Akina

Reputation: 42622

SELECT prodId, tblProduct.title, minprices.pricePcs
FROM tblProduct
JOIN ( SELECT prodId, MIN(pricePcs) pricePcs
       FROM tblPriceVariant
       GROUP BY prodId ) minprices USING (prodId)

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You can try to use subquery to make it.

Query 1:

SELECT *,(SELECT MIN(pricePcs) 
        FROM tblPriceVariant pv
        WHERE p.prodId = pv.prodId) pricePcs
FROM tblProduct p

Results:

| prodId |    title | pricePcs |
|--------|----------|----------|
|   1001 | title001 |     5000 |
|   1002 | title002 |    40000 |
|   1003 | title003 |     2500 |

Upvotes: 1

Related Questions