I'm getting wrong record in sql select query

Question

I want to select data from MySQL. And also wanted to do some calculation at query execution time. I have two SQL tables:

Loan Table :

loan_id|Customer_name|Total_amount|Collector
1000   |Sashika      |55000       |Kapoor
1001   |Amell        |11000       |Kapoor
1002   |Oliver       |15000       |Kapoor

Settlement Table :

Sett_id|Loan_id|Amount
a123    |1000    |1000
b123    |1000    |1000

Specially I wanted point that 1001 & 1002 loans not yet received any settlements. I want to select loans by collector name. and select should be like this:

Output Table

Loan_ID| Customer_Name|Total_Amount|Total_Received|Total_Due
1000   |Sashika       |55000       |2000          |53000
1001   |Amell         |11000       |0             |11000
1002   |Oliver        |15000       |0             |15000

To do that I used below select query. But It only show 1000(loan_id) record. I think because only 1000(loan_id) has settlement record. 1001 & 1002 hadn't any records that's why it doesn't show. But my requirement is load all loans details like above output Table .

Currently I use below code:

select
loan.loan_id as 'Loan ID'
   ,loan.customer_name as 'Customer Name'
   ,loan.total_amount as 'Total Amount'
   ,ifnull(sum(settlement.amount),0) as 'Total Received'
   ,((loan.total_amount)-ifnull(sum(settlement.amount), 0))as 'Total Due'
from loan
inner join settlement  on  loan.loan_id =  settlement.loan_id
where loan.collector_name='kapoor'
group by loan.loan_id

Like I said It doesn't show 1001, 1002 (loan_id's) records like Output Table

Answer 1

A left join between the above two tables is required in this case.

select
 loan.loan_id AS 'Loan ID',
 loan.customer_name AS 'Customer Name',
 loan.total_amount AS 'Total Amount',
 ifnull(sum(settlement.amount),0) AS 'Total Received',
((loan.total_amount)-ifnull(sum(settlement.amount), 0))as 'Total Due'

FROM
    loan
LEFT JOIN settlement ON loan.loan_id = settlement.loan_id
WHERE
    loan.collector_name = 'kapoor'
GROUP BY    loan.loan_id