MySQL query – join 4 tables together, with 3 tables using group by one column from each

Here are examples of the 4 tables I’m working with.

Items
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | def  |
|  3 | ghi  |
+----+------+


Buy Table
+----+-------------+-----+---------+
| id | date        | qty | item_id |
+----+-------------+-----+---------+
|  1 | 2020-05-01  | 10  |    1    |
|  2 | 2020-05-02  | 20  |    2    |
|  3 | 2020-05-03  | 5   |    3    |
+----+-----------+-------+---------+


Rent Table
+----+-------------+-----+---------+
| id | date        | qty | item_id |
+----+-------------+-----+---------+
|  1 | 2020-05-02  | 5   |    2    |
|  2 | 2020-05-03  | 10  |    2    |
|  3 | 2020-05-04  | 15  |    3    |
+----+-----------+-------+---------+


Sell Table
+----+-------------+-----+---------+
| id | date        | qty | item_id |
+----+-------------+-----+---------+
|  1 | 2020-05-03  | 10  |    1    |
|  2 | 2020-05-05  | 20  |    3    |
|  3 | 2020-05-06  | 5   |    3    |
+----+-----------+-------+---------+

And I’m trying to get outputs with php foreach something like this …

In case item_id "1"
+-------------+--------------+---------------+---------------+------+
| date        | BUY SUM(qty) | RENT SUM(qty) | SELL SUM(qty) | Name |
+-------------+--------------+---------------+---------------+------+
| 2020-05-01  |       10     |       0       |       0       | abc  |
| 2020-05-02  |       0      |       0       |       0       | abc  |
| 2020-05-03  |       0      |       0       |       10      | abc  |
| 2020-05-04  |       0      |       0       |       0       | abc  |
| 2020-05-05  |       0      |       0       |       0       | abc  |
| 2020-05-06  |       0      |       0       |       0       | abc  |
+-------------+--------------+---------------+---------------+------+

This is the query I’ve come for one table…

SELECT date AS date, 
       SUM(qty) AS qty 
FROM buy_table 
WHERE item_id='1' 
AND MONTH(date)=MONTH(CURDATE()) 
GROUP BY DATE(date)

Answer

You can cross join the items table with all available dates in the three other tables, and then the aggregations from the three tables with left joins:

select d.date, b.qty_buy, r.qty_rent, s.qty_sell, i.name
from items i
cross join (
    select date from buy 
    union all select date from rent 
    union all select date from sell
) d
left join (select date, item_id, sum(qty) qty_buy  from buy  group by date, item_id) b
    on b.date = d.date and b.item_id = i.id
left join (select date, item_id, sum(qty) qty_rent from rent group by date, item_id) r
    on r.date = d.date and r.item_id = i.id
left join (select date, item_id, sum(qty) qty_sell from sell group by date, item_id) s
    on s.date = d.date and s.item_id = i.id
where i.id = 1 and d.date >= date_format(curent_date, '%Y-%m-01')
order by d.date