19 Replies Latest reply on Oct 6, 2006 6:42 PM by Newsgroup_User

    QofQ?

    Level 7
      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.


        • 1. Re: QofQ?
          Level 7
          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
          • 2. Re: QofQ?
            Dan Bracuk Level 5
            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.
            • 3. Re: QofQ?
              Level 7
              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


              • 4. Re: QofQ?
                Dan Bracuk Level 5
                Then do what Adam told you to do.
                • 5. Re: QofQ?
                  Level 7
                  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


                  • 6. Re: QofQ?
                    Level 7
                    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?


                    • 7. Re: QofQ?
                      Level 7
                      > 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
                      • 8. Re: QofQ?
                        Level 7
                        Ummm...That last bit threw me off..


                        • 9. Re: QofQ?
                          Level 7
                          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?


                          • 10. Re: QofQ?
                            Dan Bracuk Level 5
                            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.
                            • 11. Re: QofQ?
                              Level 7
                              > Ummm...That last bit threw me off..

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

                              --
                              Adam
                              • 12. Re: QofQ?
                                Level 7
                                Nevermind, I figured it out.


                                • 13. Re: QofQ?
                                  Level 7
                                  > Nevermind, I figured it out.

                                  Good stuff!

                                  --
                                  Adam
                                  • 14. Looping
                                    Level 7
                                    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>


                                    • 15. Re: Looping
                                      Level 7
                                      You can pass <cfmail> a query. Why don't you use that instead of the loop?

                                      --
                                      Adam
                                      • 16. Re: Looping
                                        Level 7
                                        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>


                                        • 17. Re: Looping
                                          Level 7
                                          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
                                          • 18. Re: Looping
                                            Level 7
                                            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.


                                            • 19. Re: Looping
                                              Level 7
                                              > 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