QofQ?

[Newsgroup_User]

I imported a csv and created a query object . yay. The query object is
called 'SendList' using:
<cfhttp url=" http://mywebsite/Mailer/campaign/csv/#campaign.csv#"
name="sendlist" method="get" textqualifier="">

Now I need to 'compare' that query object to another Recordset / database
table to produce a final list (emailList). hmmm.

I planned on using:
<cfquery name="emailList" datasource="**">
SELECT E.email, E.name
FROM SendList E LEFT JOIN optout O
ON E.email = O.email
WHERE O.email Is Null
<cfquery>

to produce a final list, but can't figure out how to 'compare' the 2 items
to produce that list. Would I use query of queries using my 'SendList'?

Any help would be appreciated.

[Newsgroup_User]

You seem to be on the right track, but...

> <cfquery name="emailList" datasource="**">

Drop the datasource attribute and use dbtype="query" instead.

> SELECT E.email, E.name
> FROM SendList E LEFT JOIN optout O

You cannot use JOIN syntax in a QoQ. You can only use the notation:

FROM table1,table2
WHERE table1.PK = table2.FK

Are you sure you want an OUTER join for your requirement, anyhow?

--
Adam

[Dan_Bracuk]

you need two Q of Q's

QofQ1, get all the records from output where email is not null

QofQ2, select stuff from sendlist where the email is not in the valuelist from QofQ1.

[Newsgroup_User]

I need to compare the imported (emailList) against the database (Optout) and
produce a list of what is not the same. The emailList is an imported list of
email addresses for a bulk mailer. The Optout (as you can imagine) is people
who opt'd out of our mailings. I need to then do a bulk mailing with the
results of whats left.

For example:
emailList Optout
joe@me.com joe@me.com
sue@sue.com sue@sue.com
mike@mike.com john@john.com

The results would only be mike@mike.com

[Dan_Bracuk]

Then do what Adam told you to do.

[Newsgroup_User]

I am a little confused on the tables I need to select. Can I do this?

<cfquery name="maillist" dbtype="query">
SELECT email, name
FROM emailList,Optout
WHERE email.PK = email.FK AND email.FK Is Null
</cfquery>

Sorry, a wee bit lost on the PK and FK

"Adam Cameron" <adam_junk@hotmail.com> wrote in message
news:1fyie2rt5fc35$.e0wcnm0j5syj.dlg@40tude.net...
> You seem to be on the right track, but...
>
>> <cfquery name="emailList" datasource="**">
>
> Drop the datasource attribute and use dbtype="query" instead.
>
>> >> FROM SendList E LEFT JOIN optout O
>
> You cannot use JOIN syntax in a QoQ. You can only use the notation:
>
>
>
> Are you sure you want an OUTER join for your requirement, anyhow?
>
> --
> Adam

[Newsgroup_User]

Ahh, PK = primary key, FK = foreign key

<cfquery name="maillist" dbtype="query">
SELECT email, name
FROM emailList,Optout
WHERE email.PK = email.FK AND email.FK Is Null
</cfquery>

So how close is the query above to outputting all the names from the first
query (emailList) that do not appear in Optout?

[Newsgroup_User]

> Ahh, PK = primary key, FK = foreign key

Yes. But your query below is being a bit literal. Mine was example code,
you weren't supposed to copy it verbatim. I mean "[use primary key here]",
and "[use foreign key here]".

> <cfquery name="maillist" dbtype="query">
> SELECT email, name
> FROM emailList,Optout
> WHERE email.PK = email.FK AND email.FK Is Null

WHERE E.email = O.email
AND O.email Is Null

Now, if you NEED to use an outer join, you're perhaps best not attempting
this approach. Whilst it's POSSIBLE to effect an outer-join-esque record
set with QoQ, it's more hassle than it's worth.

1) Stick with the two queries you've got.
2) Extract the email column from optout with valueList().
3) Loop over SendList, so you definitely hit all records.
4) For each record of SendList, do a listFindNoCase() on the list extracted
in (2).
5) If there's a match, then use the index of the match to access the
appropriate row in optout, to extract the data from optout that corresponds
to the current record of SendList.

This will only work if there are no null values in optout.email, due to
limitations of CF's ability to handle null-data in lists.

Make sense?

--
Adam

[Newsgroup_User]

Ummm...That last bit threw me off..

[Newsgroup_User]

Ok for some odd reason this seems to work, but not giving me the results I
am looking for:

SELECT sendlist.email, sendlist.name
FROM sendlist, Optouttable
WHERE sendlist.email = Optouttable.email AND Optouttable.email Is Null

I need this:
SELECT sendlist.email, sendlist.name
FROM sendlist LEFT JOIN Optouttable ON sendlist.email = Optouttable.email
WHERE Optouttable.email Is Null

How can I do this with your (or another) method?

[Dan_Bracuk]

If your optout table contains records of people who should not receive mail, how could you possibly have null values in the email field?

If you have any control over the db design, you should consider changing the optout table with a field in the table that stores e-mail addresses.

[Newsgroup_User]

> Ummm...That last bit threw me off..

Which bit? And what about it did you not understand?

--
Adam

[Newsgroup_User]

Nevermind, I figured it out.

[Newsgroup_User]

> Nevermind, I figured it out.

Good stuff!

--
Adam

[Newsgroup_User]

I am having a problem with looping over a query.
I have a query that contains all the people who have not opted out of a
list. I want to send them an email. I am using the query to specifiy the
loop, but I keep getting all the emails sent to the top address. I need it
to step and repeat through each person. What am I doing wrong?

<cfloop query="mailist">
<cfmail from="#campaign.emailfrom#" to="#mailist.email#"
replyto="#campaign.emailreply#" server="***" subject="#campaign.subject#">
<cfmailpart type="html">
<cfoutput>#campaign.html#</cfoutput>
</cfmailpart>
<cfmailpart type="text">
<cfoutput>#campaign.plaintext#</cfoutput>
</cfmailpart>
</cfmail>
</cfloop>

[Newsgroup_User]

You can pass <cfmail> a query. Why don't you use that instead of the loop?

--
Adam

[Newsgroup_User]

Well that didn't seem to work too well. Each person on the list got 2 emails
and the message was supposed to be 'hello' but was:

'hello' on the first email and
'hellohellohellohellohello' on the second second..

There was a total of 4 people on the list...

Help!

cfif IsDefined ('Send_Mail')>
<cfmail from="#campaign.emailfrom#" to="#mailist.email#"
replyto="#campaign.emailreply#" server="***" subject="#campaign.subject#"
query="mailist">
<cfmailpart type="html">
<cfoutput>#campaign.html#</cfoutput>
</cfmailpart>
<cfmailpart type="text">
<cfoutput>#campaign.plaintext#</cfoutput>
</cfmailpart>
</cfmail>
<cflocation url="done.cfm" addtoken="no">
</cfif>

[Newsgroup_User]

Wally, I think you might be best served at least TRYING to help yourself
before you come squawking to us. Have you read the documentation for how
<cfmail> works? Maybe you should.

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001592.htm

If you follow that lot through, you should be able to work out where you're
going wrong.

I don't mind helping you out, but you should be doing at least a minimum
level of self-help before you start asking other people to do your work for
you (which is what a lot of your postings here pretty-much equate to).

If - having RTFMed - you still can see it, sing out.

--
Adam

[Newsgroup_User]

Actually I did the cfmail and the looping correctly. That was not the issue.
I normally do self posting forms, without an action page, with calls to CFCs
for functionality. The issue was that I tried coding it on the same page
while still doing a self posting form. Since part of the code was inserting
the converted csv doc to a temp database, by submitting the form to a self
post, it was reinserting it to make doubles.

I apologize for 'squawking' to the forum for help. Please, in the future,
ignore posts with my name on them.

[Newsgroup_User]

> I apologize for 'squawking' to the forum for help. Please, in the future,
> ignore posts with my name on them.

Don't get all silly. Like I said, I'm happy to help, just provided you try
to help yourself first.

Do you really think anything I said in my preceeding post is either
inaccurate or inappropriate advice?

--
Adam