Problem to calculate in MySQL

Hi guys, I have a last question :slight_smile:
The question: what is the price of materials to manufacture product XXXX

This is my tables:

  1. Product (product_name, product_price, BANR(PK)
  2. Get (BANR(FK), RID(FK)
  3. Recipe (receipe_name, r_update, RID(PK)
  4. IN (basic_goods_amount, goods_measure, RID(FK), IID(FK)
  5. Ingredient (ingredient_name, IID(PK)
  6. Deliver(purchase_amount, purchase_measure, purchase_price)
    All products have a unique recipe with a number of basic goods.
    Basic goods are purchased in quantities from a supplier.
    To answer the question I should choose a product and check out how much the ingredients cost and how much it needs.

I think I must use the following calculation:

purchase_price (table: Deliver) / purchase_amount (table: Deliver) * goods_measure (table: In).

The result should roughly look like this:

Product: name … Total Cost
cheesecake … 10

Please help me, I´ve no idea how to solve this problem

I don’t think that calculation is ok. What do goods_measure and purchase_measure contain? By any chance an ingredient can be delivered in kilograms, and used in the recipe in grams?

If the measures are not of interest, then the calculation would be (I think):
Cost = (purchase_price (table: Deliver) / purchase_amount (table: Deliver) ) * basic_goods_amount (table: In).

But, why is that column called ‘basic_goods_amount’ ? Why that ‘basic’ ?

As far as getting all the needed info from your database, you’ll have to join some tables. Start by figuring out what tables you need to join, and what the join criteria are.