Calculate using 3 tables

Transact-SQL
1

Hi,

I have these 3 tables

Product
    +-----------+-------------+----------------------+
    | ProductID | ProductName | ProductStandardPrice |
    +-----------+-------------+----------------------+
 Uses
    +------------+-----------+------------------+
    | MaterialID | ProductID | QuantityRequired |
    +------------+-----------+------------------+
RawMaterial
    +------------+-----------------------+
    | MaterialID | MaterialStandardPrice |
    +------------+-----------------------+

Uses.ProductID = Product.ProductID
Uses.MaterialID = RawMaterial.MaterialID

I want to calculate the total cost for each product. Each product uses certain quantity for each material used and each material has standard price.

So I should get

+-----------+----------------------+-----------+
| ProductID | ProductStandardPrice | TotalCost |
+-----------+----------------------+-----------+
2

Sounds like a simple INNER JOIN on the 3 tables should get you the result unless there are some quirks - e.g. duplicate entries

SELECT p.ProductID,
	p.ProductStandardPrice,
	SUM( u.QuantityRequird * r.MatgerialStandardPrice) as TotalCost
FROM
	Product p
	INNER JOIN Uses u ON
		u.ProductId = p.ProductId
	INNER JOIN RawMaterial r ON
		r.MaterialId = u.MaterialId
GROUP BY
	p.ProductID,
	p.ProductStandardPrice
1 Like
3

Thank you, yes it works