query relating four tables in MySQL

Question

hello I have a problem when generating a query between 4 tables in my database, first I explain a little the structure of my tables and how they are related:

Brand

+--------------+
| id |  name   |
|--------------|
| 1  |  toyota |
| 2  |  suzuki |
| 3  |  mazda  |
+--------------+

Car

+------------------------------------+
| id  | brand_id    | model          |
--------------------------------------
| 1   | 1           | XXXXX          |
| 2   | 2           | YYYYY          |
| 3   | 1           | ZZZZZ          |
+------------------------------------+

Rental

+------------------------------------------------+
| id | car_id | date_init  | date_end   | amount |
--------------------------------------------------
| 1  | 1      | 2019-01-05 | 2019-01-12 |3000    |
| 2  | 2      | 2019-01-22 | 2019-01-30 |1800    |
| 3  | 3      | 2019-02-14 | 2019-01-26 |2500    |
+------------------------------------------------+

Maintenance

+------------------------------------+
| id  | rental_id   | amount         |
--------------------------------------
| 1   | 3           | 600            |
| 2   | 3           | 320            |
| 3   | 2           | 180            |
+------------------------------------+

Basically what these tables do is register the rent of a vehicle during a determined time, this rental time has a clearly amount. The detail is that during the rental time you can perform any or many maintenance to the car for that reason I have the maintenance table. What I need to get from my query is how much money and generated by car model this includes the maintenance amounts, I should get something like that:

+-----------------+
| brand   | total |
|-----------------|
| toyota  | 4000  |
| suzuki  | 3800  |
| mazda   | 2700  |
+-----------------+

I have managed to obtain the amounts based only on the rent, but I do not understand how I can also relate the amounts of the maintenance table, this is what I have in my query:

select b.name as brand, sum(r.amount) as Total, m.mnt
from rental r
left join (
  select rental_id, sum(amount) as mnt
  from maintenance 
  group by m.rental_id
) as m on r.id = m.rental_id
inner join car c
on r.car_id = c.id
inner join brand b
on c.brand_id = b.id
group by b.name, m.mnt

taking into account the query that I have just published, this is the answer:

+-------------------------+
| brand   | total |  mnt  |
|--------------------------
| toyota  | 3000  | NULL  |
| toyota  | 2500  | 920   |
| suzuki  | 1800  | 180   |
+-------------------------+

NOTE: separates the amount of rent and maintenance in different columns so that the problem is better understood, in the end I need to have a single column adding the totals both.

Answer 1

You need to use a subquery for the sum of each table. Then you can join them, combine rental and mnt, and then sum this by brand.

SELECT b.name, SUM(r.rental + IFNULL(m.mnt, 0)) AS total
FROM brand AS b
JOIN car AS c on b.id = c.brand_id
JOIN (
  SELECT car_id, SUM(amount) AS rental
  FROM rental
  GROUP BY car_id) AS r ON r.car_id = c.id
LEFT JOIN (
  SELECT r.car_id, SUM(m.amount) AS mnt
  FROM rental AS r
  JOIN maintenance AS m ON m.rental_id = r.id
  GROUP BY rental_id) AS m ON m.car_id = r.car_id
GROUP BY b.id

DEMO

| name   | total |
| ------ | ----- |
| toyota | 6420  |
| suzuki | 1980  |