SQL Query Help 1

[redoakhg]

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)# [highlight #A40000]<!---In testing this returns 8 elements as it should – These elements are Newsletter ID numbers (15395,15399,15400,15404,15408,15409,15425,15423)--->[/highlight]
</cfoutput>

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

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

[highlight #CC0000]<!—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’[/highlight]
</cfquery>

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

 

[cfSearching]

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.

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.

<!--- 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/

 

[redoakhg]

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

 

[cfSearching]

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>

 

[cfSearching]

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:
[ul]
[li]Client / Columns: ClientID, ClientName, .... (Unique clients)[/li]
[li]NewsLetter / Columns: NewsLetterID, NewsLetterTitle, .. (Unique newsletters)[/li]
[li]ClientNewsLetter / Columns: ClientID, NewsLetterID (Subscriptions by client)[/li]
[/ul]

You could use EXISTS to return only clients with a matching record in the subscription table. Something like:

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">
           ) 
    )

 

[redoakhg]

Thank you, thank you, thank you! Worked great!