Setup a test page where you can CFOutput the 22 results. This way you can
see what data is being pulled and adjust the Where statement until you get
the right results.
At 12:16 PM 12/1/00 +0000, you wrote:
>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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
