CODEWITHSUNDEEP
phpmysqlsql
Zsoca
Zsoca

Reputation: 358

Query the minimum price from mysql table

I have 2 tables:

One table contain a different laptop specifications 

laptop:
|partNumber|picture|laptopName|laptopProcessor|visibility|
..........................................................
|AB00001   |pic1   |lenovo..  |intel core I5  |1         |
|AB00002   |pic2   |samsung   |AMD semprom 3.8|1         |

The other table is an inventory table, I have a multiple shops every shop has a different price for the specific laptops

inventory:
|partNumber|shopName|price|qty|
...............................
|AB00001   |shop1   |554  |2  |
|AB00002   |shop1   |356  |4  |
|AB00002   |shop3   |400  |1  |
|AB00001   |shop4   |625  |5  |
|AB00002   |shop4   |345  |3  |

I want to write a query which make a list about the laptops with the best price so I wrote this:

SELECT laptop.partNumber, laptop.picture, laptop.laptopName, laptop.laptopProcessor,
       inventory.MIN(price)
FROM laptop INNER JOIN inventory ON inventory.partNumber=laptop.partNumber
WHERE visibility="1"

So basically I want to show all laptop with the best price, it is working without price query, but I cannot add this MIN() function to the query I think I miss the syntax, but I don't know how should be work. Please help me.

Upvotes: 1

Views: 906

Answers (3)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

You are missing the Group BY

SELECT laptop.partNumber, laptop.picture, laptop.laptopName, laptop.laptopProcessor,
       inventory.MIN(inventory.price)
FROM laptop INNER JOIN inventory ON inventory.partNumber=laptop.partNumber
WHERE visibility="1"
GROUP BY laptop.partNumber, laptop.picture, laptop.laptopName, laptop.laptopProcessor

Upvotes: 1

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

The syntax is MIN(column name), so it should be MIN(inventory.price). But you also need to add a GROUP BY clause, otherwise you will have only one result for the whole table.

Then, since you want to group by laptop, add GROUP BY inventory.partNumber. If partNumber is not a primary key of laptop table, you should also add other columns.

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

Try this:

SELECT laptop.partNumber,
  laptop.picture,
  laptop.laptopName,
  laptop.laptopProcessor,
  MIN(inventory.price)
FROM laptop
INNER JOIN inventory ON inventory.partNumber = laptop.partNumber
WHERE visibility = "1"
GROUP BY laptop.partNumber,
  laptop.picture,
  laptop.laptopName,
  laptop.laptopProcessor

Upvotes: 1

Related Questions