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 .......

  • 3 Contributors
  • 2 Replies
  • 268 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Bukola_1
Member Avatar for hericles

How many settlement records are there for 'Kapoor'?

commented: more than two +1
Member Avatar for Bukola_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.