Bob,
I have modified my query as you suggested - and it's come up with some
`interesting` results...
Instead of returning 2 records, which it should have, it returned 22 (and
incidentally email all 22!) Here's the query:
<Cfquery name="invoices" datasource="localads">
SELECT
advert_details.body,advert_details.subject,advert_details.end_date,advert_de
tails.start_date,advert_details.ID,advert_details.email,ad_ID,advert_ID,paym
ents.paid,sent FROM payments,invoices,advert_details
WHERE payments.paid = Yes AND invoices.sent = No AND payments.ad_ID =
advert_ID
</cfquery>
What am I missing? I think it's pulling all adverts from advert_details
which match an email address in payments or invoices.
Thanks
Will
----- Original Message -----
From: ""Bob Silverberg"" <[EMAIL PROTECTED]>
Newsgroups: dotcom.lists.cftalk
Sent: Friday, December 01, 2000 9:18 AM
Subject: FW: Advanced queries
> OK, it's late and I'm not operating at 100%. Looking at this again, you
> could rewrite the whole thing into one query that joins the payments,
> invoices and advert_details together. Then use CFOUTPUT with the GROUP
> attribute to generate as many (or as few) emails and updates as you like.
>
> At least I think the update query I gave you was correct.
>
> Bob
>
> -----Original Message-----
> From: Bob Silverberg [mailto:[EMAIL PROTECTED]]
> Sent: November 30, 2000 6:32 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Advanced queries
>
>
> Without any report of error messages it's hard to say if there's anything
> wrong with your code. It looks like it _could_ run OK. Did you try it?
I
> think you may want to move the CFMAIL tag inside of the query loop, but
I'm
> not totally sure of what you're trying to do. You also may want to scope
> your variables in the second query with the query name (i.e.,
invoices.ad_ID
> and invoices.email).
>
> To set the sent column to yes, include the following in your loop:
>
> <Cfquery name="update_invoice" datasource="localads">
> UPDATE invoices
> SET sent = Yes
> WHERE WHERE ID = #invoices.ad_ID# AND email = '#invoices.email#'
> </cfquery>
>
> I'm assuming you're using Access, and therefore the yes and no without
> quotes should be ok.
>
> Bob
>
>
> -----Original Message-----
> From: W Luke [mailto:[EMAIL PROTECTED]]
> Sent: November 30, 2000 12:15 PM
> To: CF-Talk
> Subject: Advanced queries
>
>
> 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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
