Posted by Jerry Stuckle on 04/26/06 15:34

Bob Bedford wrote:
> Thanks for replying, Andy,
>
> I've no access to any mysql newsgroup with my ISP, that's why I ask here.
>
> Here is my query:
>
> SELECT person.IDProprietary, FirstName, LastName,
> DATE_FORMAT(StartDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,
> billpro.IDBill, DateBill, AmountBill, SUM(paymentpro.AmountPayment) as
> totalpayment from person inner join abos on person.IDProprietary =
> abos.IDProprietary inner join typevendeur on person.IDTypeVendeur =
> typevendeur.IDTypeVendeur left join billpro on abos.IDAbo = billpro.IDAbo
> left join paymentpro on billpro.IDBill = paymentpro.IDBill WHERE
> typevendeur.IDTypeVendeur > 1 group by paymentpro.IDPayment HAVING
> AmountBill > totalpayment ORDER BY FirstName
>
> the problem is that I've no result with "having AmountBill > totalpayment"
> as there is no payment at all (I guess).
>
> I'd like to have all records where the totalpayment < AmountBill but can't
> figure out how. What's wrong with my query ?
>
> Bob
>
>
>

Bob,

My suggestion -

1) Ask your ISP to carry comp.databases.mysql
2) Use Google Groups - it carrys all newsgroups
3) Use another news server

The point is - you will get a much better answer to your question if you ask in
the appropriate group - that's where the experts on that topic hang out! "My
ISP doesn't carry X group" isn't a valid excuse any more. Too many other options.

As to your question. First of all, try formatting your SQL so it's more readable:

SELECT person.IDProprietary, FirstName, LastName,
DATE_FORMAT(StartDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,
billpro.IDBill, DateBill, AmountBill,
SUM(paymentpro.AmountPayment) as totalpayment
FROM person
INNER JOIN abos ON person.IDProprietary = abos.IDProprietary
INNER JOIN typevendeur ON person.IDTypeVendeur = typevendeur.IDTypeVendeur
LEFT JOIN billpro ON abos.IDAbo = billpro.IDAbo
LEFT JOIN paymentpro ON billpro.IDBill = paymentpro.IDBill
WHERE typevendeur.IDTypeVendeur > 1
GROUP BY paymentpro.IDPayment
HAVING AmountBill > totalpayment
ORDER BY FirstName

First of all, I think you're getting output if there is a partial payment, but
I'm not sure from your description of the problem.

As to not getting any output if there is no payment - that would be correct. In
this case totalpayment would be NULL, and any comparison to NULL is false.

You might try changing your HAVING clause to (not tested):

HAVING AmountBill > totalpayment OR totalpayment IS NULL



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]