To many iterations of value in MySQL Query

[imperium2335]

Hi,

 

I am having problems with the following query:

 

$result = mysql_query("SELECT DATE(invoices_out.invoiceDate) AS date,
				  invoices_out.jobRef,
				  invoices_out.branch,
				  invoices_out.customer, 
				  invoices_out.currency, 
				  invoices_out.absoluteTotal, 
				  invoices_out.euroVpound, 
				  invoices_out.dollarVpound,
				  sum(if(pos.currency = '$', pos.amount/pos.dollarRate, 0))+sum(if(pos.currency = '€', pos.amount/pos.euroRate, 0))+sum(if(pos.currency = '£', pos.amount, 0)) AS totalCosts,
				  enquiries.theirShippingTotal,
				  enquiries.theirShippingCurrency,
				  pourbaskets.coreCreditBuy,
				  pourbaskets.shippingTotal,
				  pourbaskets.currency,
				  pourbaskets.shippingCurrency,
				  partbaskets.coreCreditSell
				  FROM invoices_out, pos, enquiries, pourbaskets, partbaskets
				  WHERE pos.jobRef = invoices_out.jobRef
				  AND enquiries.id = invoices_out.enqRef
				  AND pourbaskets.enquiryRef = invoices_out.enqRef
				  AND partbaskets.enquiryRef = invoices_out.enqRef
				  GROUP BY invoices_out.jobRef, pos.jobRef, pourbaskets.enquiryRef, partbaskets.enquiryRef")or die(mysql_error()) ;

 

It seems to be adding up totalCosts for every instance of a row that has the same enquiryRef in partbaskets. If I take partbaskets and pourbaskets out of the entire query, it returns the right value.

 

What am I missing?

[Muddy_Funster]

Have you tried changing the order of your GROUP BY to have the enquieryRef at the start?