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

IssueTable:

ReceiptTable:

The desired result that I want:

Balance:

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.

CodePudding user response：

You may try using a join approach:

SELECT it.`Order`, it.Type, it.Qty - rt.Qty AS Qty
FROM IssueTable it
INNER JOIN ReceiptTable rt
    ON rt.`Order` = it.`Order` AND rt.Type = it.Type;

This answer assumes that every order would have a matching receipt. If not, the approach might have to change slightly based on your expectations. As a side note, ORDER is a reserved keyword in MySQL, and you should avoid naming your columns and tables using it.

CodePudding user response：

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.