CODEWITHSUNDEEP
mysql
Tristan
Tristan

Reputation: 21

How can I multiply a result by a specific value from another table?

A simplification of my problem. Say I have these tables:

Supply:

Boxes:

Basically want I want to do is:

SELECT supplyName, numberOfSupply GROUP BY supplyName

from these tables. But I want numberOfSupply to be quantityPerBox*numBoxes.

Can this be done as a query? Or would I need to store the quantities from the Supply table in a program in some language (PHP for instance) and do the calculations there? (Or is there really no benefit from doing the calculation as part of the query, even if it's possible?)

Thank you for your help in advance. It's appreciated.

Upvotes: 2

Views: 261

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17560

This can certainly be done in a query without having to store that value separately, or using a different language:

SELECT s.supplyName, s.quantityPerBox * b.numBoxes AS NumberOfSupply
FROM Supply AS s
INNER JOIN Boxes AS b ON s.supplyName = b.supplyName

You probably want to add SupplyId to both tables though so you have an integer value to JOIN on: more efficient than trying to JOIN on a VARCHAR field. Another benefit is that if you ever wanted to change the supplyName of an item, you would not have to worry about foreign key issues when making that change if you were referencing tables by ID instead of a value within the table.

Upvotes: 1

Related Questions