Data Subtractions From Two Mysql Tables Problem-CodePudding

I have two tables

Products table

id	item_code	item_name dept	date_received	quantity_received
1	ELM001	Jelly ICT	2021-12-16	20
2	ELM001	Jelly ICT	2021-11-23	10
3	MVT001	CARB MVT	2021-09-17	15
4	HBT001	DISK HBT	2021-08-25	12
5	MVT001	CARB MVT	2020-12-12	10

issue_products

id	item_code	date_issued	issued_to	quantity_issued
1	ELM001	2021-12-17	John	12
1	MVT001	2021-12-16	Dave	10

expected results

available_products

item_code	item_name	dept	quantity_received	quantity_issued balance
ELM001	Jelly	ICT	30	12
HBT001	DISK	HBT	12	0
MVT001	CARB	MVT 25	10	15

My code

SELECT
                item_code,
                item_name,
                dept,
                sum(total1) as quantity_received,
                sum(total2) as quantity_issued,
              SUM(total1) - SUM(total2) AS balance
            FROM (SELECT id, item_code,
           cons_received AS total1, 0 AS total2
            FROM Products_table
            UNION ALL
            SELECT id,item_code,
              0 AS total1, cons_issued AS total2
      FROM issue_products) a   GROUP BY item_code

am getting error

unknown columns

CodePudding user response：

SELECT item_code,
       MAX(item_name) item_name,
       MAX(dept) dept,
       sum(total1) as quantity_received,
       sum(total2) as quantity_issued,
       SUM(total1) - SUM(total2) AS balance
FROM ( SELECT id, item_code, cons_received AS total1, 0 AS total2, item_name, dept
       FROM Products_table
     UNION ALL
       SELECT id, item_code, 0, cons_issued, NULL, NULL
       FROM issue_products) a   
GROUP BY item_code;