Hi, I wonder if someone could help me out with some queries I'm running on a scheduled task. I need to send out outstanding invoices, including details of what they have bought (i.e. an Advert - details of which are in advert_details). Once done, I need to set the "Sent" column of the Invoices to "Yes". It involved 3 tables - invoices, payments and advert_details. Below is what I've done - but I'm pretty sure I've done it wrong, and I'm not sure where or how to set the invoices.sent to "Yes". <Cfquery name="invoices" datasource="localads"> SELECT email,ad_ID,advert_ID,paid,sent FROM payments,invoices WHERE paid = Yes AND sent = No AND ad_ID = advert_ID </cfquery> <Cfif #invoices.recordcount# LT 1> There are no outstanding INVOICES TO BE PAID <Cfelse> <Cfoutput query="invoices"> <cfquery name="getuser" datasource="localads"> SELECT * FROM advert_details WHERE ID = #ad_ID# AND email = '#email#' </cfquery> </cfoutput> <cfmail query="getuser" to="#email#"> <!--- send out invoice ---> </cfmail> There are <cfoutput>#invoices.recordcount# invoices outstanding</cfoutput> </cfif> I'd be grateful for anyone's wisdom with advanced querying on this.. Cheers Will ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
