CODEWITHSUNDEEP
mysqlsqljoingroup-bysum
n1ck
n1ck

Reputation: 21

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)

Upvotes: 1

Views: 60

Answers (1)

GMB
GMB

Reputation: 222472

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

Upvotes: 1

Related Questions