Help with MySQL Query

alc4emy5t, if you need only records from table CreditNote related to one Invoice you need execute following query:

Select SUM(MonthCharge), SUM(TotalCharge)
From Invoice RIGHT JOIN CreditNote ON InvoiceRef = InvoiceNo
where Invoice.CustID = 'wnet10692';

I hope this query pull out information that you need.

Thank you for your comments but neither of them are giving the result that i want.

Basically, It is a One to Many relationship between tables Invoice and CreditNote.
One Invoice can have Many CreditNotes. Here is an example

Invoice = $50
CreditNote = $10
CreditNote = $20

Invoice = $20
CreditNote = $10

Invoice = $30

For the First Invoice:
We have an Invoice amount of $50 and a total credit of $30, if we subtract the credit amount from the Invoice amount we should be left with $20.

For then second Invoice:
We have an Invoice amount of $20 and a total credit of $10, if we subtract the credit amount from the Invoice amount then we should be left with $10

For the Third Invoice:
We have an Invoice Amount of $30 and a total credit ot $0, if we subtract the credit amount from the Invoice amount then we should be left with $30
 
Therefore the customer is Owning $10 + $20 + $30 = $60

the query should look like this:

Select SUM(InvoiceAmount - IFNULL(SUM(CreditAmount), 0)) From Invoice LEFT OUTER JOIN CreditNote ON InvoiceRef = InvoiceNo Where CustID = 'wnet10692'

and the result returned should be $60 but actually the result returned is $110
The reason is because the resulting table will look like the following

InvoiceAmount       CreditAmount
$50                       $10
$50                       $20
$20                       $10
$30                       $0

It will duplicate the InvoiceAmount because of the extra record created from the creditamount
The it will SUM the InvoiceAmount and subtract it from the SUM of the CreditAmount giveing a result of $110

But I want the result table to look like the following

InvoiceAmount       CreditAmount
$50                       $30
$20                       $10
$30                       $0

so that the sum gives the correct value of $60 or atleast the resulting table could look like

InvoiceAmount       CreditAmount
$50                       $10
$0                         $20
$20                       $10
$30                       $0

So if anyone has any clues how to solve this using one query please let me know, im getting rather desperate, The version of MySQL doesnt support subqueries

alc4emy5t try with the following query:

Select SUM(MonthCharge), (select SUM(TotalCharge) from CreditNote where InvoiceRef = InvoiceNo)
From Invoice where Invoice.CustID = 'wnet10692';

Tortega 2001, thank you for your comment but as i mentioned in my last post the version of MySQL im using doesnt support sub queries

Thank again tortega 2001, but although your query works its not actually what i wanted, as mentioned above i am trying to find the total sum of the customers invoice - the total sum of the customers credits. therefore i will be left with one correct figure of $60 but instead im getting an incorrect figure of $110

Your query displays the values correctly but when i rewrite your query as

Select SUM(Invoice.InvoiceAmount), SUM(CreditNote.CreditAmount)
From Invoice LEFT OUTER JOIN CreditNote ON InvoiceRef = InvoiceNo
Where Invoice.CustID = 'wnet10692' group by Invoice.InvoiceAmount

then its still outputting
InvoiceAmount       CreditAmount
$50                       $10
$50                       $20
$20                       $10
$30                       $0

instead of

InvoiceAmount       CreditAmount
$50                       $30
$20                       $10
$30                       $0

So here again is the query im executing

Select SUM(Invoice.InvoiceAmount) - SUM(CreditNote.CreditAmount)
From Invoice LEFT OUTER JOIN CreditNote ON InvoiceRef = InvoiceNo
Where Invoice.CustID = 'wnet10692';

and I get just one value of $110, because this query is duplicating one of the invoice amounts because there are two creditamounts for that invoice.

I don't have MySql here to test, but I wonder if you can do this

Select SUM(Invoice.InvoiceAmount), SUM(CreditNote.CreditAmount)
From Invoice LEFT OUTER JOIN CreditNote ON InvoiceRef = InvoiceNo
Where Invoice.CustID = 'wnet10692'
group by SUM(Invoice.InvoiceAmount)

it just seems like you need to have another field in your table so that you can group that way, something like

Select InvoiceNumber, SUM(Invoice.InvoiceAmount), SUM(CreditNote.CreditAmount)
From Invoice LEFT OUTER JOIN CreditNote ON InvoiceRef = InvoiceNo
Where Invoice.CustID = 'wnet10692'
group by InvoiceNumber

Sorry but that didnt work either it still duplicates the invoice amount, my tables look like this

Invoice Table
 CustID       | varchar(12)
 DueDate      | datetime
 InvoiceDate  | datetime
 InvoiceNo    | int(11) Primary Key
 Memo         | text
 MonthCharge  | float
 MonthReceive | float
 OperatorID   | varchar(10)
 PayID        | int(11)
 PaymentType  | varchar(50)

CreditNote Table
CreditNoteID | int(10) Primary Key
CustID       | varchar(10)
InvoiceRef   | int(11) Foreighn Key
CreateDate   | date
TotalCharge  | float
OperatorID   | varchar(20)
Memo         | text

Please note that InvoiceAmount = Monthcharge and CreditAmount = TotalCharge, I just changed the names to make it more simple.

MySQL doesnt support cast for non string values, I basically dont want the $50 to be duplicate so i dont think cast would help to solve this problem.  I am beginning to think that maybe there isnt any way to solve this with one query.

BlackTigerX,

if using this:
Select Invoice.InvoiceAmount, SUM(CreditNote.CreditAmount)
From Invoice LEFT OUTER JOIN CreditNote ON InvoiceRef = InvoiceNo
Where Invoice.CustID = 'wnet10692' group by Invoice.InvoiceAmount

does not give this result:  <<<<<<<<<<<
InvoiceAmount       CreditAmount
$50                       $10
$50                       $20
$20                       $10
$30                       $0

he changed first line in above SELECT statement. See his post.

HI Andrew, sorry about the long wait, that looks very fine but it get an SQL error, it doesnt like having distinct in that section of the query.

The result should be a single value of the SUM(MonthCharge) - SUM(TotalCharge), but as i was trying to explain before, if there are to values of totalcharge for one value of monthcharge then it duplicates and sums the monthcharge.  maybe this formula can explain it

MonthCharge = MonthCharge * Number of TotalCharge

Becuase there are two records to totalcharge, when you do a join mysql will duplicate the monthcharge to match the number of records fo totalcharge and then sum the result. so if you have:

$50 (monthcharge) $10 (TotalCharge)
                             $20 (TotalCharge)

and you do a join on the two tables with invoiceref = invoiceno then the following happens

$50 (monthcharge) $10(TotalCharge)
$50 (monthcharge) $20 (TotalCharge)

So SUM(monthcharge) - SUM(TotalCharge) = $70 but the correct answer show be $20 because the SUM(monthcharge) = $50 and SUM(TotalCharge) = $30 therefore
SUM(MonthCharge) - SUM(TotalCharge) should = $20