• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

How to do this query in ColdFusion

I have a table called myUsers with 1000 UserID's.  This table is the result of a query.  

I have another table called EmailSent that records every email sent to a UserID and which EmailTemplate was used.

How do I get the subset of those 1000 myUsers that have not yet received  EmailTemplate = "Template1.cfm"

This is so I can send the remainder of the UserIDs, the email.

I could do this with spaghetti code but prefer to do it within a CFquery tag.
0
bigmikey88
Asked:
bigmikey88
  • 9
  • 7
  • 4
  • +1
1 Solution
 
NerdsOfTechTechnology ScientistCommented:
Join the records on UserID and filter for the parameter EmailTemplate != "Template1.cfm":

 SELECT *
 FROM EmailSent e
 INNER JOIN myUsers u
 WHERE e.EmailTemplate != "Template1.cfm"
 ON u.UserID = e.UserID

Open in new window


edit: Use INNER JOIN if EmailSent has more than the 1000 Uses filtered for in myUsers.
0
 
gdemariaCommented:
@NerdOfTech - I think you're statement is a bit out of order?

 SELECT u.*
 FROM myUsers u
    left join  EmailSent e  ON u.UserID = e.UserID  and e.EmailTemplate = "Template1.cfm"
 WHERE e.userId is null

Open in new window


This will also work...
 SELECT u.*
 FROM myUsers u
where not exists (select 1 
      from  EmailSent e  
      where u.UserID = e.UserID  
     and e.EmailTemplate = "Template1.cfm"
     )

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
Oops, I put the ON after WHERE. Sorry about that.

In hindsight, I should've first asked this question to the OP:

Is there always a record in EmailSent for User?

If not, a left join and an OR e.UserID IS NULL is needed.

 SELECT *
 FROM myUsers u
 LEFT JOIN EmailSent e
 ON u.UserID = e.UserID
 WHERE e.EmailTemplate != "Template1.cfm" 
 OR e.UserID IS NULL

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bigmikey88Author Commented:
Hi NerdsOfTech,

Can u see anything wrong with what I've done?  Can't get it to work.

<cfquery name="CheckEmailsSent"  datasource="#Dynamic_Visitors#"  dbtype="ODBC"  >
SELECT *
 FROM EmailSent e
 INNER JOIN GetTestedUsers u
 WHERE e.TemplateUsed != 'PTestTemplate1A.cfm'
 ON u.UserID = e.UserID
</cfquery>
0
 
NerdsOfTechTechnology ScientistCommented:
<cfquery name="CheckEmailsSent"  datasource="#Dynamic_Visitors#"  dbtype="ODBC"  >
 SELECT *
 FROM GetTestedUsers u /*changed per your last post*/
 LEFT JOIN EmailSent e
 ON u.UserID = e.UserID
 WHERE e.EmailTemplate != "PTestTemplate1A.cfm" /*changed per your last post*/
 OR e.UserID IS NULL
</cfquery>

Open in new window


However, I would like to point out that @gdemaria stated this correct query first (yet the WHERE on mine I think is correct) and the other expert should be given credit.
0
 
gdemariaCommented:
Sorry, but this query will not work either

 SELECT *
 FROM myUsers u
 LEFT JOIN EmailSent e
 ON u.UserID = e.UserID
 WHERE e.EmailTemplate != "Template1.cfm"
 OR e.UserID IS NULL

You want to exclude the emailSent record with "Template1"  therefore you must have that part of the condition on the JOIN.
Putting it on the where clause may return multiple records for each user.   If user XXX  had three emails sent to him, and one was the Template1, then that user record will be joined to two records and the user XXX will appear twice.

You want to eliminate only the records with Template1 and NOT join the others.

 SELECT u.*
 FROM myUsers u
    left join  EmailSent e  ON u.UserID = e.UserID  and e.EmailTemplate = "Template1.cfm"
 WHERE e.userId is null


This specifies only the template you want to avoid
    left join  EmailSent e  ON u.UserID = e.UserID  and e.EmailTemplate = "Template1.cfm"

This ensure that record is not returned
 WHERE e.userId is null

The NOT exists option is easier to understand and works just as well
0
 
bigmikey88Author Commented:
Hi gdemaria,

I tried your last soution but I'm doing something wrong:
       
 
 <cfquery name="CheckEmailsSent"  datasource="#Dynamic_Visitors#"  dbtype="ODBC"  >
SELECT u.*
 FROM GetTestedUsers u
 Left Join EmailSent e ON u.UserID = e.UserID
 and e.TemplateUsed = "PTestTemplate1A.cfm"
 WHERE e.UserID is null  
 
 </cfquery>
 
 Dynamic_Visitors selects the database that holds these tables
GetTestedUsers is a query that holds the UserID I want to look at
0
 
PortletPaulfreelancerCommented:
Here is another method for arriving at the wanted subset:

SELECT
      e.userId
FROM EmailSent e
GROUP BY userId
HAVING COUNT(CASE WHEN e.EmailTemplate = 'Template1.cfm' THEN 1 END) = 0
;

SELECT
      u.*
FROM myUsers u
INNER JOIN (
      SELECT
            userId
      FROM EmailSent
      GROUP BY userId
      HAVING COUNT(CASE WHEN EmailTemplate = 'Template1.cfm' THEN 1 END) = 0
      ) e ON u.UserID = e.UserID
;

Open in new window

0
 
PortletPaulfreelancerCommented:
try using single quotes not double

SELECT u.*
 FROM GetTestedUsers u
 Left Join EmailSent e ON u.UserID = e.UserID
 and e.TemplateUsed = 'PTestTemplate1A.cfm'
 WHERE e.UserID is null
0
 
bigmikey88Author Commented:
Here is my latest - but still not working.
 
 <cfquery name="CheckEmailsSent"  datasource="#Dynamic_Visitors#"  dbtype="ODBC"  >
SELECT u.*
 FROM GetTestedUsers u
 Where Not Exists (
 Select 1
      From  EmailSent e  
      Where u.UserID = e.UserID  
     AND e.TemplateUsed = 'PTestTemplate1A.cfm'
 
 )
  </cfquery>
0
 
gdemariaCommented:
Is this a contest for the most complex query?  lol :)

GetTestedUsers is a query that holds the UserID I want to look at

Are you saying that GetTestUsers is the name of a CFQUERY statement, not the database table?
If that is true, then remove that and use the name of the database tables instead
You cannot join a cfquery with a database table

if you did not mean that, then please explain.

(agreed, that you should use single quotes instead of double quotes)
0
 
bigmikey88Author Commented:
gdemaria, sorry no contest going on  - but I am sure it feels like it... sorry

GetTestUsers is indeed the name of a query.  I'm guilty.

I actually ran a query on two different databases to get my list of UserIDs.  Thats what GetTestUsers is. Then tried to use that in the contest query.

Since I can not do that, can I do a query where I use three different tables?
0
 
gdemariaCommented:
Since I can not do that, can I do a query where I use three different tables?

Yes, for sure.   If you post that query we can help you merge all three tables into one query.
0
 
bigmikey88Author Commented:
OK ready for a query with 3 pieces

The query below finds users who have completed a personality test in the last few days.  It runs several times a day then shoots out an email when ready.

FYI the personality type test is given away free then we try to sell a report later down the road.  The test is good enough that Princeton Univ uses it in a graduate class on Entrepreneurship to help grad students develop self awareness before they unleash themselves upon the unsuspecting universe.

The query makes sure the user had not been flagged as undeliverable and that they have not asked to have emails stopped

<cfquery name="GetTestedUsers"  datasource="#Dynamic_Visitors#"  dbtype="ODBC"  >
Select u.UserID, u.Email, cs.OrgNumber, cs.YourType, U.StopEmail,u.Undeliv, u.DoNotEmail
From  CP_PT_UserData.dbo.Client_Scores cs
 Join
CP_VisitorsDB.dbo.Users u   ON  cs.UserID = u.UserID
Where cs.YourType IS NOT NULL AND cs.TestDateTime_Start > #TimeLimit# AND cs.OrgNumber = 0 AND u.Undeliv = 0
AND u.StopEmail = 0 AND u.DoNotEmail = 0 AND cs.GroupTest2 <>1
order by u.UserID
</cfquery>

that query results in about 1035 users out of 350,000 who have taken the test recently.  

Then I want to send them an email using TemplateUsed = PTestTemplate1A.cfm  if they have not already been sent that email.

I would like for the query to have a "maxrows" limit on it of say 100, so that I limit the rate of outgoing emails so as not to upset the email gods at Yahoo, Gmail etc.  

All emails sent are are logged in the table "EmailSent"    this table includes UserID, DateSent, TemplateUsed etc

You might wonder why I don't just use Constant Contact for this?  Have you ever seen the monthly price when you have > 300K subscribers

Hope you can help.  I'm looking forward to learning how to do this.

Michael
0
 
gdemariaCommented:
just added the NOT EXISTS at the end..

Select u.UserID, u.Email, cs.OrgNumber, cs.YourType, U.StopEmail,u.Undeliv, u.DoNotEmail
From  CP_PT_UserData.dbo.Client_Scores cs
 Join CP_VisitorsDB.dbo.Users u ON  cs.UserID = u.UserID
Where cs.YourType IS NOT NULL 
AND cs.TestDateTime_Start > #TimeLimit# 
AND cs.OrgNumber = 0 AND u.Undeliv = 0
AND u.StopEmail = 0 
AND u.DoNotEmail = 0 
AND cs.GroupTest2 <>1
and not exists (
select 1 
      from  EmailSent e  
      where u.UserID = e.UserID  
     and e.EmailTemplate = "Template1.cfm"
)
order by u.UserID

Open in new window

0
 
bigmikey88Author Commented:
thx gdemaria  
i will try it later tonight or in the morning  and let you know
michael
0
 
gdemariaCommented:
I would like for the query to have a "maxrows" limit on it of say 100, so that I limit the rate of outgoing emails so as not to upset the email gods at Yahoo, Gmail etc.  

This is a challenge.   Sending 100 at a time with 5 minute intervals could take you over a week to mail to your list.
along those lines, one thing I've done is to arrange the emails by domain so you limit the number of emails for each recipient's domain.

That is, you cap the number of emails sent to each domain in any given batch.   Let's say you cap it to 25 per domain.  That means you can send 25 to gmail, 25 to yahoo, 25 to every individual company, etc.   Each domain would not see more than 25 incoming emails from you at a time but you could potentially send out thousands of emails if they are all going to different places.   If gmail is the most popular, it may take you a while to get all of them out, but at least you have the rest out more quickly.
0
 
NerdsOfTechTechnology ScientistCommented:
Many hosts have spam relay limits for outgoing emails, within certain time parameters.

It is likely that the OP is adhering to their limits, rather than recipients' limitations.

Arranging by domain may have consequences that may backfire, since it may be considered flooding by one or a few domains.

Naturally dispersed emails (normalized sequencing) would probably be optimal.
0
 
bigmikey88Author Commented:
I'm actually OK with dribbling out the emails.  Got used to it.  But yes it can take a week or two or more to mail a full list. They are not time sensitive.

I have my own email server software "SmarterMail" running on my main production server, so I can control the outgoing bandwidth.  

I could sort the emails by domain- as suggested here. May think about that for the future.

These days I have to skip anything going to Hotmail or it will go right into people's spam folder.  Hotmail is the worst.  We are clean enough to get into Yahoo and Gmail.  We are not on any black lists.

Of course I could just pay Constant Contact or some other service to take care of delivery issues.  But as I said - very expensive.  Plus, my system is tightly integrated with what people do on my web site, what they buy, or use, free samples they want, which end market they represent.  

OK time to go try that query ...
0
 
bigmikey88Author Commented:
thx gdemaria,

Your solution worked flawlessly.

Your approach takes 400ms to run.  The prior approach which I wrote many years ago was taking 30 sec.  That had too much spaghetti code and too many queries.

Your approach wrapped it all up in one big query.

Thank you very much
Michael
0
 
gdemariaCommented:
great news.

make sure you have a database index on  EmailSent  (userID, emailTemplate)
0
 
bigmikey88Author Commented:
got it  thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now