Member Avatar for සශික

I used below select query to select information from two tables. But it show one record. How to change this code to view all records ?

select
    loan.loan_id,
    loan.customer_name,
    loan.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
    settlement.loan_id = loan.loan_id
where
    loan.collector_name='kapoor'

There are two loans which are collected by kapoor. But it show one record.
Guys help me .......

Recommended Answers

All 2 Replies

How many settlement records are there for 'Kapoor'?

commented: more than two +1

try this

select
    l.loan_id,
    l.customer_name,
    l.total_amount,
    ifnull(sum(s.amount), 0) as 'Total Received',
    ((l.total_amount) - ifnull(sum(s.amount), 0)) as 'Total Due'
from loan l, settlement s where l.loan_id = s.loan_id 
AND l.collector_name='kapoor'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.