CODEWITHSUNDEEP
mysqlsql
Guilherme Oderdenge
Guilherme Oderdenge

Reputation: 5001

MIN/MAX price for each product (query)

The goal

Select MIN/MAX price of each product in database.

The problem

I am able to get only products with specified identifiers.

What I have

I'm using MySQL and I have the follow query:

SELECT (MIN(`map`.`Product_Price`)) as `minProductPrice`,
    (MAX(`map`.`Product_Price`)) as `maxProductPrice`,
    `pr`.`Product_Name` as `productName`
FROM `bm_market_products` as `map`
JOIN `bm_products` as `pr`
JOIN `bm_markets` as `ma`
WHERE `map`.`Product_Id` = 1
AND `map`.`Product_Id` = `pr`.`Product_Id`

My return is the minProductPrice, maxProductPrice and productName.

The solution

Thank you about your help. The both answers above are right — but I chose @GordonLinoff answer's as accepted because I think it will be more useful and enjoyed by beginners — but really thanks to you both guys. The final query:

SELECT MIN(`map`.`Product_Price`) as `minProductPrice`,
       MAX(`map`.`Product_Price`) as `maxProductPrice`,
       `pr`.`Product_Name` as `productName`
FROM `bm_market_products` `map` join
     `bm_products` as `pr`
     on map`.`Product_Id` = `pr`.`Product_Id`
group by `map`.`Product_Id`

Cheers!

Upvotes: 3

Views: 4953

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

First, when you use join, you should always have an on clause, even though MySQL does not require this. If you want a cross join, then be explicit about it.

Second, you don't use the tm_markets table at all in the query. It is not needed, so remove it.

The resulting query should work:

SELECT MIN(`map`.`Product_Price`) as `minProductPrice`,
       MAX(`map`.`Product_Price`) as `maxProductPrice`,
       `pr`.`Product_Name` as `productName`
FROM `bm_market_products` `map` join
     `bm_products` as `pr`
     on map`.`Product_Id` = `pr`.`Product_Id`
WHERE `map`.`Product_Id` = 1

Because you are only choosing one product, a group by is probably not necessary. You might consider this, however:

SELECT MIN(`map`.`Product_Price`) as `minProductPrice`,
       MAX(`map`.`Product_Price`) as `maxProductPrice`,
       `pr`.`Product_Name` as `productName`
FROM `bm_market_products` `map` join
     `bm_products` as `pr`
     on map`.`Product_Id` = `pr`.`Product_Id`
group by `map`.`Product_Id`

That will return the information for all products.

Upvotes: 4

000
000

Reputation: 27247

SELECT (MIN(`map`.`Product_Price`)) as `minProductPrice`,
    (MAX(`map`.`Product_Price`)) as `maxProductPrice`,
    `pr`.`Product_Name` as `productName`,
    `map`.`Product_Id` 
FROM `bm_market_products` as `map`
JOIN `bm_products` as `pr`
JOIN `bm_markets` as `ma`
WHERE `map`.`Product_Id` = `pr`.`Product_Id`
GROUP BY `map`.`Product_Id`

Upvotes: 4

Related Questions