How to calculate the quantity of products (that composes beverages) in the sale of beverages?

Question

I have the following configuration:

#purchases
id
date
supplier_id

#purchase_line
product_id
purchase_id
quantity
price

#products
id
name
description
measure

#recipe
product_id
beverage_id
quantity

#beverage
id
description
method

#sale_line
beverage_id
sale_id
quantity
price

#sales
id
date

Basically, a sale has many beverages and each beverage is made by certain quantity of products. So, what is the best approach to calculate the quantity of products in each sale?

It is part of an inventory control, where I will have to compare the sales (in terms of products, not beverages) to the purchases.

Answer 1

Is this along the lines of what you're looking for? If not, post some more information. The query below joins up the sales, sales line, beverage, recipe, and products tables, and then calculates the quantity sold based on the quantity of beverages sold and the amount in the recipe. I didn't test this. You can add a WHERE clause to restrict based on date, product, etc, as desired.

QUANTITY SOLD:

SELECT pr.prod_id
  , pr.prod_name
  , SUM(sl.qty*rc.qty) AS qty_sold 
FROM sales sa JOIN sales_line sl ON sa.sales_id = sl.sales_line_id 
              JOIN beverage bv   ON sl.bev_id = bv.bev_id
              JOIN recipe rc     ON bv.bev_id = rc.bev_id
              JOIN products pr   ON rc.prod_id = pr.prod_id
WHERE ...
GROUP BY pr.prod_id
  , pr.prod_name

QUANTITY PURCHASED:

SELECT pr.prod_id
  , pr.prod_name
  , SUM(pl.qty) AS qty_purch
FROM purchase pc JOIN purchase_line pl ON pc.purch_id = pl.purch_id
                 JOIN products pr      ON pl.prod_id = pr.prod_id
WHERE ...
GROUP BY pr.prod_id
  , pr.prod_name