Vincent Collins’ Weblog

Problem

Write a tool that allows you to email all users. No one should get more than one email even if they are on the list more than twice.

The Catch

The twist to this problem is that the table of users is set up with a primary and a secondary email field and the same email can appear in multiple records in either the primary or secondary field.

Example Table

In the above example, We have Tim twice in Email1 and once in Email2. If we did a SQL Group by we would still have Tim twice. Once in Email1 and Once in Email2. It wouldn’t take long before Tim is calling asking us to fix this annoying problem of getting two emails.

The Solution

ColdFusion Query of Query and the good ol’ UNION feature in SQL!

<cfquery name="qryUsers" datasource="#request.dsn#">
	Select Email1 as Email
	From Users
	Group by Email1

	UNION

	SELECT Email2 AS Email
	FROM Users
	Group by Email2
</cfquery>
<cfquery name="qryEmails" dbtype="query">
	Select Email
	From qryUsers
	Group by Email
</cfquery>

The above example does two queries. The first one qryUsers uses UNION to combine two queries of the same table both retrieving email fields. With UNION you need to make sure that you name the fields the same from both queries and that they are both of the same data type. The second query qryEmails is a simple ColdFusion Query of Query which then takes the resulting recordset and groups once again to get rid of the second instance of Tim. This can also work with two different tables in the event you have a user table and maybe a Admin table but you want to send one email to everyone.

Now, isn’t programming fun?

There are other ways to solve this common problem. I’d love to hear yours!

It’s probably old news to folks who have been using Query of Query a lot but now with CFFEED, some developers may be using the query of query feature for the first time.

CFFEED when converting to a query has no knowledge of data type for any given column. In the example below, google’s news feed returns a column (PUBLISHEDDATE) that is treated as a string but is in fact a date. Thus when you try to sort by PUBLISHEDDATE, it doesn’t work the way you would expect. By using the cast() function, I’m able to on the fly sort the query as a date.

<!--- set feed --->
<cfset feed = "http://news.google.com/?output=rss">
<!--- grab feed --->
<cffeed action="read" source="#feed#" query="rss" userAgent="Mozilla/4.0" properties="rssProperties">
<!---  Sort query by date after casting it as a date --->
<cfquery dbtype="query" name="rss">
SELECT
Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
FROM
RSS
ORDER BY
publisheddate desc
</cfquery>