Send email to DISTINCT email

Question

Hi there,

I have an Classic ASP page that lets users search for properties and a results page that then emails the property agents who have matching properties to let them know that there are tenants interested in their properties.

Some agents may have more than 1 property, in which case the agent only needs to receive 1 email rather than an email for each property they have. Currently my code is doing the latter and I need it to only send 1 email to each agent.

In the code below I have 2 recordsets; rspropertyresults displays all matching properties and then I have rsemailagents which is used to gather the agent's email address and send them an email.

I was wondering if anyone might be able to see how I can amend the sql query for rsemailagents to send out 1 email per agent?

<%
[...]
rsemailagents.Source = "SELECT *"
rsemailagents.Source = rsemailagents.Source& "FROM    ("
rsemailagents.Source = rsemailagents.Source& "        SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "        FROM    ("
rsemailagents.Source = rsemailagents.Source& "                SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "                FROM    VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& "                WHERE   ContentStreet = '" & Replace(rsemailagents__varReqStreet, "'", "''") & "'"
rsemailagents.Source = rsemailagents.Source& "                UNION ALL"
rsemailagents.Source = rsemailagents.Source& "                SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "                FROM    VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& "                WHERE   ContentTown = '" & Replace(rsemailagents__varReqTown, "'", "''") & "'"
rsemailagents.Source = rsemailagents.Source& "                UNION ALL"
rsemailagents.Source = rsemailagents.Source& "                SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "                FROM    VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& "                WHERE   ContentTrimmedPostCode LIKE '" & Replace(varPostcode, "'", "''") & "%'"
rsemailagents.Source = rsemailagents.Source& "                ) qi"
rsemailagents.Source = rsemailagents.Source& "        GROUP BY"
rsemailagents.Source = rsemailagents.Source& "                ContentID"
rsemailagents.Source = rsemailagents.Source& "        HAVING  COUNT(*) >= 2"
rsemailagents.Source = rsemailagents.Source& "        ) q "
rsemailagents.Source = rsemailagents.Source& "JOIN    VWTenantPropertiesResults r "
rsemailagents.Source = rsemailagents.Source& "ON      r.ContentID = q.ContentID "
rsemailagents.Source = rsemailagents.Source& "WHERE   ContentBedrooms BETWEEN " & Replace(rsemailagents__varBedroomsNoMin, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND     " & Replace(rsemailagents__varBedroomsNoMax, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND     ContentPrice BETWEEN " & Replace(rsemailagents__varPriceMin, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND     " & Replace(rsemailagents__varPriceMax, "'", "''") & " " & varSQL & " "
rsemailagents.Source = rsemailagents.Source& "ORDER BY"
rsemailagents.Source = rsemailagents.Source& "        ContentPrice " & Replace(rsemailagents__varSortWay, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "GROUP BY CustomerEmail"

[...]
%>

In my email code I have set the objMail.To as follows;

objMail.To = (rsemailagents.Fields.Item("CustomerEmail").Value)

This is the VWTenantPropertiesResults code;

SELECT     dbo.VWResidentialLettings.ContentID, dbo.VWResidentialLettings.ContentTitle, dbo.VWResidentialLettings.ContentBriefText, 
                      dbo.VWResidentialLettings.ContentDetails, dbo.VWResidentialLettings.ContentHouseNo, dbo.VWResidentialLettings.ContentStreet, 
                      dbo.VWResidentialLettings.ContentStreet2, dbo.VWResidentialLettings.ContentTown, dbo.VWResidentialLettings.ContentArea, 
                      dbo.VWResidentialLettings.ContentCounty, dbo.VWResidentialLettings.ContentPostCode, dbo.VWResidentialLettings.ContentReference, 
                      dbo.VWResidentialLettings.ContentPrice, dbo.VWResidentialLettings.ContentPricePeriod, dbo.VWResidentialLettings.ContentPriceText, 
                      dbo.VWResidentialLettings.ContentPropertyType, dbo.VWResidentialLettings.PropertyTypeTitle, dbo.VWResidentialLettings.ContentPropertyListType, 
                      dbo.VWResidentialLettings.PropertyListTypeTitle, dbo.VWResidentialLettings.PricePeriodTitle, dbo.VWResidentialLettings.BedRoomNoTitle, 
                      dbo.VWResidentialLettings.ContentBedRooms, dbo.VWResidentialLettings.ContentFurnishing, dbo.VWResidentialLettings.FurnishTypeTitle, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerID, dbo.VWPropertyAgentsActiveSubscriptions.CustomerName, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerBusiness, dbo.VWPropertyAgentsActiveSubscriptions.CustomerAddress1, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerAddress2, dbo.VWPropertyAgentsActiveSubscriptions.CustomerCity, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerArea, dbo.VWPropertyAgentsActiveSubscriptions.CustomerRegion, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerPostalCode, dbo.VWPropertyAgentsActiveSubscriptions.CustomerPhone, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerFax, dbo.VWPropertyAgentsActiveSubscriptions.CustomerURL, 
                      dbo.VWPropertyAgentsActiveSubscriptions.CustomerEmail, dbo.VWResidentialLettings.CountyTitle, dbo.VWResidentialLettings.ContentCreated, 
                      dbo.VWResidentialLettings.ContentUpdated, dbo.VWResidentialLettings.ContentStatus, dbo.VWPropertyImagesList.DocumentFile, 
                      dbo.VWPropertyImagesList.DocumentTitle, dbo.VWResidentialLettings.ContentTrimmedPostCode, dbo.VWPropertyImagesList.ContentStatusTitle
FROM         dbo.VWResidentialLettings INNER JOIN
                      dbo.VWPropertyAgentsActiveSubscriptions ON 
                      dbo.VWResidentialLettings.ContentParentID = dbo.VWPropertyAgentsActiveSubscriptions.CustomerID LEFT OUTER JOIN
                      dbo.VWPropertyImagesList ON dbo.VWResidentialLettings.ContentID = dbo.VWPropertyImagesList.ContentID
WHERE     (dbo.VWResidentialLettings.ContentStatus = 2)

When I run this query in the database, it is just returning a CustomerEmail column and nothing else;

SELECT DISTINCT CustomerEmail
FROM    (
    SELECT  ContentID
    FROM    (
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentStreet = 'Hull'
                UNION ALL
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentTown = ''
                UNION ALL
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentTrimmedPostCode LIKE 'HU7'
                ) qi
        GROUP BY
                ContentID
        HAVING  COUNT(*) >= 2
        ) q
JOIN    VWTenantPropertiesResults r
ON      r.ContentID = q.ContentID
WHERE   ContentBedrooms BETWEEN 1 AND 10 AND ContentPrice BETWEEN 1 AND 10

Answer 1

Put your query into the IN clause of this query

SELECT  email
FROM    agents
WHERE   id IN
        (
        SELECT  agentID
        FROM    …
        )

This will select each agent only once.

Answer 2

Ok...

Try to create a subquerry that will group all propperties together in a single line.

You can do this by creating a group by query like this:

select contentID , count(*) hits from VWTenantPropertiesResults where proppertyValue in ('A','B','C') group by contentID

Also, you are vulnerable to SQL Injection attackes, since you create your SQL without propperly escaping it. - Consider a parameterized query here, or a stored procedure.

You also have a very complicated way of creating your query. You build a string in 10+ steps, and assign it to the propperty. Consider using a stringbuilder here, since strings are immutable and this can cause performance issues (esp. when something like this is used in a loop... If you need more then 5 concats, then think about using the stringbuilder)

Answer 3

The subquery you have aliased 'q' has a single field, ContentID. Unless you have very weird naming conventions, that's not an email address. Thus, you're telling it to "Select CustomerEmail From (table-like object that does not have a field called CustomerEmail)." That cannot possibly work, and I'm surprised it returns anything other than an error message.

Try looking into either the 'IN' clause or the 'EXISTS' clause.