合并多行然后离开join-php mysql

Question

I have two tables: invoices and sales. These are simplified version of them:

Invoices:

InvoiceNo  |  ProductNo  |  QtyIn   
-----------------------------------
F01        |  00001      |  20
F01        |  00002      |  50
F01        |  00003      |  15
F02        |  00002      |  10

Sales:

Date    |  ProductNo  |  QtyOut   
---------------------------------
3/2/17  |  00002      |  12
3/3/17  |  00002      |  8
3/4/17  |  00003      |  10

What I'm trying to do is to make a stock summary table, which looks like this:

ProductNo  |  QtyIn  |  QtyOut  |  Stock
-------------------------------------------
00001      |  20     |  0       |  20
00002      |  60     |  20      |  40
00003      |  15     |  10      |  5

To make that stock table, as far as I can think is:

• Make another database for invoice where same product will be merge and it's quantity will be sum up.
• Make something like the first one for sales.
• Then make a join table for both of them.

By this way, I will end up having 5 tables. Is there any simpler way to do this? Thanks.

Answer 1

If you apply a filer on Invoices and Sales (e.g. for a date range) either filtered result could have products not referenced in the other. Hence a single outer join does not cover all possible conditions. As there is no "full outer join" in MySQL a simple method to overcome this is to union all source rows then group that:

select ProductNo, sum(QtyIn) QtyIn, sum(QtyOut) QtyOut, sum(QtyIn) - sum(QtyOut) Stock
from (
      select ProductNo, QtyIn, 0 as QtyOut
      from invoices 
        # where datecol >= `2017-01-01`
      union all
      select ProductNo, 0 as QtyIn, QtyOut
      from Sales
        # where datecol >= `2017-01-01`
     ) u
group by ProductNo