Help with my crazy query!

[kmaid]

Hi all I could use some help with this query i am working on. I have a transaction table with the following structure.

 

TransactionID - int(11), Primary

Date - datetime

ClientID - int(11)

PaymentType - varchar(50)

Reference - varchar(200)

AmountUSD - decimal(8,2)

AmountGBP - decimal(8,2)

Amount - decimal(10,2)

Currency - varchar(4)

 

I am trying to get a monthly total of all transactions not marked as an expense or target and then attach that month's target to the result which is marked with 'Target' in the PaymentType. aka Month, Sum(`GBPAmount`), `Target`

 

Here is my query so far. I think i am nearly there but its doing my head in.

SELECT date( T1.Date ) AS Date,
sum( T1.AmountGBP ) AS Sales

FROM transactions AS T1
INNER JOIN transactions AS T2 
ON month( T1.Date ) = month( T2.Date )

WHERE T1.PaymentType NOT IN ('Target', 'Expense')
AND T2.PaymentType = 'Target'

GROUP BY month( T1.Date )

 

its valid but doesnt return any results. If i remove the "AND T2.PaymentType = 'Target'" part i get a huge sum from joining every record to each other. I think i may need a subquery rather than a join but it seeemed overkill initially. Any help even if its just a pointer would be most appreciated

 

Edit: MySQL client version: 5.1.41

[kmaid]

Still stuck on this one ://. Should I be doing this as two separate queries? I really don't want to use multiple queries unless I absolutely need to. Goes against the grain to have to manipulate data like this after using a query.

[Mchl]

Are you sure there are records fulfiiling these criteria? The query looks fine to me.

[kmaid]

Thanks.......  :'( way too much time debugging that query instead of checking the data.