query doesn't return all results

[enrico via DotNetMonster.com]

this is my select query behind my form with a condition:

SELECT tbltrcclient.Cl ientID, tbltrcservice.S erviceID, tbltrcservice.D ate,
tbltrcclient.La stname,
tbltrcclient.Fi rstname, tbltrcclient.Ba rangay, `Sitio/Purok`, tbltrcservice.
Barangay,
tbltrcservice.N atureofService, tbltrcservice.F ree, tbltrcservice.R emarks,
tbltrcservice.N oOfHectares,
tbltrcservice.N oOfLiters, tbltrcservice.T otalAmount, tbltrcservice.
AmountRemaining ,
sum(tbltrcpayme nt.AmountPaid) FROM agri.tbltrcclie nt INNER JOIN agri.
tbltrcservice ON
(tbltrcclient.C lientID = tbltrcservice.C lientID) INNER JOIN agri.
tbltrcpayment ON
(tbltrcservice. ServiceID = tbltrcpayment.S erviceID) WHERE
(tbltrcservice. AmountRemaining = TotalAmount - AmountPaid)
GROUP BY tbltrcclient.Cl ientID;

my problem is it does not return all the result and i don't know why.

--
Message posted via DotNetMonster.c om

http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-vb-net/200804/1

[Phill W.]

Re: query doesn't return all results

enrico via DotNetMonster.c om wrote:

SELECT tbltrcclient.Cl ientID, tbltrcservice.S erviceID, tbltrcservice.D ate,
tbltrcclient.La stname,
tbltrcclient.Fi rstname, tbltrcclient.Ba rangay, `Sitio/Purok`, tbltrcservice.
Barangay,
tbltrcservice.N atureofService, tbltrcservice.F ree, tbltrcservice.R emarks,
tbltrcservice.N oOfHectares,
tbltrcservice.N oOfLiters, tbltrcservice.T otalAmount, tbltrcservice.
AmountRemaining ,
sum(tbltrcpayme nt.AmountPaid) FROM agri.tbltrcclie nt INNER JOIN agri.
tbltrcservice ON
(tbltrcclient.C lientID = tbltrcservice.C lientID) INNER JOIN agri.
tbltrcpayment ON
(tbltrcservice. ServiceID = tbltrcpayment.S erviceID) WHERE
(tbltrcservice. AmountRemaining = TotalAmount - AmountPaid)
GROUP BY tbltrcclient.Cl ientID;

I'm surprised this works at all.

All the databases I've worked with recently would insist that everything
you retrieve, except the grouped ClientID, should be aggregated or
included /in/ the Group By clause.

Anyway ...

my problem is it does not return all the result and i don't know why.

It's almost certainly your Joins; records that appear in one table but
not in the other.
Double-check your join conditions and the data underpinning them.

HTH,
Phill W.