MySQL loans, payments and clients

Question

I have 3 tables "clients", "loans" and "payments"

TABLE CLIENTS:

+------------+-------------+
| id_client  | name        |
+------------+-------------+
| 1          | James       |
| 2          | Robert      |
| 3          | Michael     |
+------------+-------------+

TABLE LOANS:

+-------------+-------------+-------------+-------------+
| id_loan     | id_client   | date_loan   | amount_loan |
+-------------+-------------+-------------+-------------+
| 1           | 2           | 2020-07-01  | 3000000     |
| 2           | 3           | 2021-07-12  | 2000000     |
+-------------+-------------+-------------+-------------+

TABLE PAYMENTS:

+-------------+-------------+--------------+----------------+
| id_payment  | id_loan     | date_payment | amount_payment |
+-------------+-------------+--------------+----------------+
| 1           | 1           | 2020-08-03   | 50000          |
| 2           | 1           | 2020-09-03   | 50000          |
| 3           | 1           | 2020-10-03   | 50000          |
| 4           | 1           | 2020-11-03   | 50000          |
| 5           | 1           | 2020-12-03   | 50000          |
| 6           | 1           | 2021-01-03   | 50000          |
| 7           | 1           | 2021-02-03   | 50000          |
| 8           | 1           | 2021-03-03   | 50000          |
| 9           | 1           | 2021-04-03   | 50000          |
| 10          | 1           | 2021-05-03   | 50000          |
| 11          | 1           | 2021-06-03   | 50000          |
+-------------+-------------+--------------+----------------+

I want to print this:

+-------------+-------------+-------------+-------------+-------------+
| id_loan     | client      | date_loan   | amount_loan | amount_left |
+-------------+-------------+-------------+-------------+-------------+
| 1           | Robert      | 2020-07-01  | 3000000     | 2450000     |
| 2           | Michael     | 2021-07-12  | 2000000     | 2000000     |
+-------------+-------------+-------------+-------------+-------------+

Please note that Michael has a loan but has no payments yet.

I'm almost done with this query:

SELECT loans.id_loan,
   clients.name,
   loans.date_loan,
   loans.amount_loan,
   (loans.amount_loan-SUM(payments.amount_payment)) AS amount_left
FROM loans
LEFT JOIN clients ON loans.id_client = clients.id_client
LEFT JOIN payments ON loans.id_loan = payments.id_loan

But Michael is obviously not listed because he doesn't have a payment made yet.

Answer 1

You just need to add a GROUP BY loans.id_loan, then the missing row should appear. Also, I would suggest changing the JOIN on clients to just JOIN.

SELECT loans.id_loan,
   clients.name,
   loans.date_loan,
   loans.amount_loan,
   (loans.amount_loan-SUM(payments.amount_payment)) AS amount_left
FROM loans
JOIN clients ON loans.id_client = clients.id_client
LEFT JOIN payments ON loans.id_loan = payments.id_loan
GROUP BY loans.id_loan

DEMO: http://sqlfiddle.com/#!9/32ca4/18