Need MYSQL uquery solution-CodePudding

I have the folowing tree tables one is the stock_items with all the items in it. The stock_in has the stock movements in to the stock and stock_out has the out movements:

and I want to get such a query result:

could some one help me to make this query?

CodePudding user response：

You want to select the stock items and join the in totals and out totals. Aggregate to get the totals. Outer join to get items with and without transactions. Use COALESCE to replace nulls with zeros.

select
  s.barcode, s.item_name,
  coalesce(si.total, 0) as amount_in,
  coalesce(so.total, 0) as amount_out,
  coalesce(si.total, 0) - coalesce(so.total, 0) as balance,
  s.unit
from stock_items s
left join
(
  select barcode, sum(amount) as total
  from stock_in
  group by barcode
) si on si.barcode = s.barcode
left join
(
  select barcode, sum(amount) as total
  from stock_out
  group by barcode
) so on so.barcode = s.barcode
order by s.barcode;

CodePudding user response：

You can join tables using the barcode column?

select * from stock_items 
join stock_in on stock_items.barcode = stock_in.barcode
join stock_out on stock_items.barcode = stock_out.barcode

Just replace the * with a list of desired column names

CodePudding user response：

Unleash the power of subqueries and then use subtraction to compute the total stock level.

Basically the strategy here is to:

Run one query to sum up the total stock in
Run another query to sum up the total stock out
Run a final query subtracting the summed values.

select *, (stock_in - stock_out) as total from (select
    product_id pid,
    product_name,
    product_unit,
    (select sum(stock_in) from stock_in where product_id=pid) as stock_in,
    (select sum(stock_out) from stock_out where product_id=pid) as stock_out
from products) summed

SQL fiddle here: https://www.db-fiddle.com/f/v95qsALSfnm66HoQb6PqJ6/0