MySQL query inner join SUM

Question

I have two tables "furesz" has all the incoming cubic meters by day. I have a "maglya" with the out come cubic meter by day. I want to make a table wher each day the in put and the out put cubic sum is shown

furesz:

wood	length	cubic	date	machine
A-001	2.5	0,885	2022-02-22	mebor1
A-001	2.5	0,655	2022-02-22	mebor1
A-003	2.5	0,785	2022-02-23	mebor1

maglya:

wood	width	cubic	date	machine
A-006	36	0,585	2022-02-22	mebor1
A-006	58	0,355	2022-02-22	mebor1
A-008	37	0,685	2022-02-23	mebor1

I tried to do it with inner join but it not work out! What should I change or do to get the following output:

Day	A-001 sum(in)	A-006 sum (out)	A-003 sum (in)	A-008 sum (out)	total sum (in)	total sum (out)
2022-02-22	1.540	0.940	0	0	1.540	0.940
2022-02-23	0	0	0.785	0.685	0.785	0.685

SELECT MONTH(date) AS month,    
SUM(IF(wood='A-001',cubic,0)) sum_nyar,
SUM(IF(wood='A-003',cubic,0)) sum_tölgy,
SUM(IF(wood='A-004',cubic,0)) sum_vtölgy,
SUM(IF(wood='A-018',cubic,0)) AS sum_cser,
SUM(cubic) AS sum_full,
FROM furesz
UNION
SELECT MONTH(date) AS month,    
SUM(IF(wood='A-006',cubic,0)) sum_m_nyar,
SUM(IF(wood='A-008',cubic,0)) sum_m_tölgy,
SUM(IF(wood='A-009',cubic,0)) sum_m_vtölgy,
SUM(IF(wood='A-017',cubic,0)) AS sum_m_cser,
SUM(cubic) AS sum_m_full,
FROM maglya
WHERE machine='mebor1'
GROUP BY month

Answer 1

Use UNION ALL first to get all the rows of both tables and then use conditional aggregation:

SELECT month,
       SUM(CASE WHEN col = 1 AND wood = 'A-001' THEN cubic ELSE 0 END) `A-001 sum(in)`,
       SUM(CASE WHEN col = -1 AND wood = 'A-006' THEN cubic ELSE 0 END) `A-006 sum(out)`,
       SUM(CASE WHEN col = 1 AND wood = 'A-003' THEN cubic ELSE 0 END) `A-003 sum(in)`,
       SUM(CASE WHEN col = -1 AND wood = 'A-008' THEN cubic ELSE 0 END) `A-008 sum(out)`,
       .................................................................................,
       SUM(CASE WHEN col = 1 THEN cubic ELSE 0 END) `total sum(in)`,
       SUM(CASE WHEN col = -1 THEN cubic ELSE 0 END) `total sum(out)`
FROM (
  SELECT MONTH(date) month, wood, cubic, 1 col FROM furesz
  UNION ALL
  SELECT MONTH(date) month, wood, cubic, -1 col FROM maglya WHERE machine = 'mebor1'
) t
GROUP BY month;