×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Query Help

SQL Query Help

SQL Query Help

(OP)
Wondering if you can help me. I have a friend who is generating a list from check boxes on a form. He then wants to retrieve clients who subscribe to newsletters based on the list, it's been 10 years since I've done any programming and I can’t remember how to do it.

This is what he has:

<cfoutput>
<cfset coldfusion_list = "#recipientlist#">
#listLen(coldfusion_list)# <!---In testing this returns 8 elements as it should – These elements are Newsletter ID numbers (15395,15399,15400,15404,15408,15409,15425,15423)--->
</cfoutput>

<cfloop index = "ListElement" list = "#coldfusion_list#">

<cfquery name="getclients" datasource="#db#">
Select clientemail
FROM VIEW_SendNotification
WHERE newsletterid IN (#ListElement#)

<!—This WHERE statement is his problem – He wants to retrieve users if they match any of the newsletter ID’s. In this case they have to match ALL newsletter ID’s and I can’t remember how to loop over the list.
Here’s what he wants it to do – WHERE newsletterid = ‘15395’ OR newsletterid = ‘15399’ OR newsletterid =’15400’ OR newsletterid =’15404’ OR newsletterid = ‘15408’ OR newsletterid =’15409’ OR newsletterid =’15425’ OR newsletterid =’15423’

</cfquery>

</cfloop>
<cfoutput query="getclients">#clientemail#<br /></cfoutput>

RE: SQL Query Help

Quote:

He wants to retrieve users if they match any of the newsletter ID’s. In this case they have to match ALL newsletter ID’s

Which one do you want: any of all? If you mean you only want clients that subscribed to all eight (8) newsletters, use a HAVING clause instead of looping.

CODE

SELECT clientemail, COUNT(*) AS TotalNewsLetters
FROM   VIEW_SendNotification
WHERE  newsletterid IN (
     15395,15399,15400,15404,15408,15409,15425,15423
) 
GROUP BY clientemail
HAVING COUNT(*) = 8 

Written dynamically, it would be something like this. Note: Assumes your list does not contain duplicate newsletter id's.

CODE

<!--- sample list --->
<cfset uniqueListOfNewsLetterIDs = "15395,15399,15400,15404,15408,15409,15425,15423">

<cfquery ...>
SELECT clientemail, COUNT(*) AS TotalNewsLetters
FROM   VIEW_SendNotification
WHERE  newsletterid IN (
        <cfqueryparam value="#uniqueListOfNewsLetterIDs#" cfsqltype="cf_sql_integer" list="true">
) 
HAVING COUNT(*) = <cfqueryparam value="#listLen(uniqueListOfNewsLetterIDs)#" cfsqltype="cf_sql_integer">
</cfquery> 

----------------------------------
http://cfsearching.blogspot.com/

RE: SQL Query Help

(OP)
What I need are clients that subscribe to ANY of the newsletters in the list. Thanks so much for your help.

RE: SQL Query Help

Then all you need is an IN clause. No loop. Just pass in the list of newsletter id's

<cfquery name="getclients" datasource="#db#">
SELECT clientemail
FROM VIEW_SendNotification
WHERE newsletterid IN (
<cfqueryparam value="#uniqueListOfNewsLetterIDs#" cfsqltype="cf_sql_integer" list="true">
)
</cfquery>

RE: SQL Query Help

Note, you might need to use SELECT DISTINCT to suppress duplicates.

Depending on your tables, another possibility is using an EXISTS clause. I do not know your table structure, but say you have:
  • Client / Columns: ClientID, ClientName, .... (Unique clients)
  • NewsLetter / Columns: NewsLetterID, NewsLetterTitle, .. (Unique newsletters)
  • ClientNewsLetter / Columns: ClientID, NewsLetterID (Subscriptions by client)
You could use EXISTS to return only clients with a matching record in the subscription table. Something like:

CODE -->

SELECT c.clientEmail
FROM   Client c
WHERE  EXISTS (
           SELECT n.ClientID
           FROM   ClientNewsLetter n
           WHERE  n.ClientID = c.ClientID
           AND    n.newsletterid IN 
           (
               <cfqueryparam value="#uniqueListOfNewsLetterIDs#" cfsqltype="cf_sql_integer" list="true">
           ) 
    ) 

RE: SQL Query Help

(OP)
Thank you, thank you, thank you! Worked great!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close