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.

What's wrong with another INNER JOIN on maintenance, with another column in your SELECT for SUM(maintenance.amount)? — Ken White, Feb 16 '19 at 02:31
@KenWhite See https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049 for what happens if you don't do it correctly. — Barmar, Feb 16 '19 at 02:38
@Barmar: Yes, I'm aware of possible issues if it's not done correctly. I don't see any effort by the poster to do it in any way (correctly or not) in the question, which is why I asked. — Ken White, Feb 16 '19 at 02:41
@Barmar I had already tried the solution proposed in the link, but the result I get is that it duplicates the rows for brand ... and the total sum of the maintenance amounts is correct but it appears in the second duplicate row, in the first I get NULL — FeRcHo, Feb 16 '19 at 14:58
@KenWhite an inner join would not work because I would only take into account the rents when they have a record in maintenance and as I mentioned these may have many like none, I tried with left join but what I mentioned in the previous comment happens — FeRcHo, Feb 16 '19 at 15:06
Show the complete query you tried and then I'll reopen and we can help you. — Barmar, Feb 16 '19 at 15:12
@Barmar edit my question adding new notes please can review them — FeRcHo, Feb 16 '19 at 15:38

score 1 · Accepted Answer · answered Feb 16 '19 at 20:56

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  |

answered Feb 16 '19 at 20:56

Barmar

741,623
53
500
612

thank you very much is just what I needed, now a question. it is possible to adapt the following to this query: if I add one more attribute to the rental table this would be a foreign key of a user table, I want to obtain the same result but from a specific user and for that, it should only take into account the rents of this user and the maintenance of that rent, is it possible to adapt this based on the query that you provided me? – FeRcHo Feb 18 '19 at 15:06
Just add appropriate `WHERE user_id = xxx` clauses the queries that include `rental`. If you want to do it by user name, join with the `users` table and then use `WHERE users.name = 'name'` – Barmar Feb 19 '19 at 03:39

query relating four tables in MySQL

1 Answers1