×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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!

*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

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! Already a Member? Login

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