Need help writing ColdFusion sql query with a JOIN

Queries that use a join always baffle me so I could use some expert advice. Thanks in advance...

How to I write a ColdFusion query where:

I have one database called "Visitors"

I have 2 tables in that database:
"Users" is a table with my entire membership listing
"EmailSent" is a table listing every newsletter sent to each member

The common field is "UserID"

Within EmailSent is a field called "TemplateUsed" which is a different template for each version of the newsletter.

I need a query that will output the UserID's of those members who did not yet receive a particular template called "ImportedCareerCoach5.cfm"

But they may have received other newsletters.

Hope that makes sense.

 I'm running CF 10 and MS SQL
bigmikey88Asked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
this will do it ..

select a.* 
from Users a
where not exists (
   select 1
   from EmailSent em
   where em.user_id = a.user_id
   and   sm.TemplateUsed = 'ImportedCareerCoach5.cfm'
   )

Open in new window

1
 
Ryan ChongConnect With a Mentor Commented:
try:

select a.* from Users a
left join 
(select * from EmailSent where TemplateUsed = 'ImportedCareerCoach5.cfm' ) b
on a.UserID = b.UserID
where b.UserID is null

Open in new window

1
 
bigmikey88Author Commented:
Thank you GDEMARIA, that worked.

Sorry for such a late reply.  I started working with your solution right away, but was getting odd results, which took a lot of analysis.  
but it's working now in production.

Thank you Ryan Chong.  Sorry I did not get to try your solution, but I will use it next time to see how it works.

Thanks to all of you
Michael
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.

All Courses

From novice to tech pro — start learning today.