Issue, Receipt and Balance from MySql table

Question

I have two tables, issue and receipt where I am issuing and receiving quantities :

IssueTable:

Order	Type	Qty
OD12	A	48
OD19	A	33
OD12	B	14

ReceiptTable:

Order	Type	Qty
OD12	A	20
OD19	A	15
OD12	B	11

The desired result that I want:

Balance:

Order	Type	Qty
OD12	A	28
OD19	A	18
OD12	B	03

IssueTable contains details of Orders which have been issued, a single order can have multiple "Type" of products. Similarly, ReceiptTable contains details of Orders which have been completed and received. I want a Balance table which subtracts issue qty from receipt qty based on Order and Type.

Answer 1

SELECT `Order`, 
       `Type`, 
       COALESCE(IssueTable.Qty, 0) - COALESCE(ReceiptTable.Qty, 0) Qty
FROM ( SELECT `Order`, `Type` FROM IssueTable
       UNION 
       SELECT `Order`, `Type` FROM ReceiptTable ) TotalTable
LEFT JOIN IssueTable USING (`Order`, `Type`)
LEFT JOIN ReceiptTable USING (`Order`, `Type`);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cafd416abcbf7ab31f54bf6efbd6566f

The query assumes that (Order, Type) is unique in each separate table. If not then use aggreagating subqueries instead if the tables itself.