Query for calculate inventory item from multiple tables

query-inventory-multiple-tables.sql

SELECT 
    inventories.product_id, 
    products.name as product_name, 
    stock_create.stock_production as total_stock_production, 
    SUM(inventories.qty) as total_stock_in, 
    SUM(distribusi.stock) as total_stock_in_warehouse, 
    SUM(inventories.qty - delivery.stock) as total_stock_current, 
    delivery.stock as total_stock_out
FROM inventories
LEFT JOIN (
    SELECT distributions.product_id, SUM(distributions.qty) as stock 
    FROM inventories, distributions
    WHERE distributions.product_id = inventories.product_id
    GROUP BY product_id
) as distribusi ON inventories.product_id = distribusi.product_id
LEFT JOIN (
    SELECT inventories.product_id, SUM(productions.qty) as stock_production
    FROM inventories, productions
    WHERE inventories.product_id = productions.product_id
    GROUP BY product_id
) as stock_create ON inventories.product_id = stock_create.product_id
LEFT JOIN (
    SELECT inventories.product_id, SUM(deliveries.qty) as stock 
    FROM inventories, deliveries
    WHERE inventories.product_id = deliveries.product_id
    GROUP BY product_id
) as delivery ON inventories.product_id = delivery.product_id
LEFT JOIN products ON products.id = inventories.product_id
GROUP BY product_id;

Query get distributions with products

SELECT 
    warehouses.name as wareshouse_name,
    products.name as product_name, 
    SUM(distributions.qty) as total_stok_in,
--  SUM(distributions.qty - kirim_stok.deliver_stok) as total_stok_current, 
    kirim_stok.deliver_stok as total_stok_out
FROM distributions
LEFT JOIN (
    SELECT deliveries.product_id, deliveries.warehouse_id, SUM(deliveries.qty) as deliver_stok 
    FROM distributions, deliveries
    WHERE distributions.product_id = deliveries.product_id
    AND distributions.warehouse_id = deliveries.warehouse_id
    GROUP BY deliveries.warehouse_id, deliveries.product_id
) as kirim_stok ON distributions.warehouse_id = kirim_stok.warehouse_id AND distributions.product_id = kirim_stok.product_id
LEFT JOIN products ON products.id = distributions.product_id
LEFT JOIN warehouses ON warehouses.id = distributions.warehouse_id
GROUP BY distributions.warehouse_id, distributions.product_id;