Subselect as condition

[imperium2335]

Hi,

 

How would I use a subselect in a WHERE clause? e.g.

 

WHERE pos.jobRef = invoices_out.jobRef
				  AND enquiries.id = pos.enqRef
				  AND DATE(invoices_out.invoiceDate) = CURDATE()
				  AND (SELECT MAX(dateCreated) FROM pos) = invoices_out.jobRef

 

Because I just want it to base its selection on the latest date result.

[imperium2335]

I've tried doing this to test how to do it but I get an error saying invalid use of group function :

 

 

require("system/dbconnectlocal.php") ;

 

$result = mysql_query("SELECT if(currency = '€', (SELECT amount FROM pos WHERE MAX(dateCreated) AND jobRef = '885'), 0) AS euroTotal FROM pos WHERE jobRef = '885'")or die(mysql_error()) ;

 

while($row = mysql_fetch_assoc($result)) {

 

echo $row['euroTotal'] ;

 

}

[suresh_kamrushi]

Try to use below one

SELECT if(currency = '€', (SELECT amount FROM pos WHERE jobRef = '885' order by dateCreated DESC limit 1), 0) AS euroTotal FROM pos WHERE jobRef = '885'")or die(mysql_error()) ;

[imperium2335]

Thanks, that works for my test, but how would I get that into something where its on a per record bases like :

 

$result = mysql_query("SELECT DATE(invoices_out.invoiceDate) AS date,
				  invoices_out.enqRef AS eId,
				  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, 0)) AS dollarTotal,
				  sum(if(pos.currency = '€', pos.amount, 0)) AS euroTotal,
				  sum(if(pos.currency = '£', pos.amount, 0)) AS poundTotal,
				  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
				  FROM invoices_out, pos, enquiries
				  WHERE pos.jobRef = invoices_out.jobRef
				  AND enquiries.id = pos.enqRef
				  #AND pos.weReceivedInvoice = 1
				  AND DATE(invoices_out.invoiceDate) = CURDATE()
				  GROUP BY pos.enqRef
				  ORDER BY invoices_out.branch")or die(mysql_error()) ;