So I've got this query... (Is that how messages here are supposed to
start?) Anyway, I have this client with a pre-existing database structure
that he made himself... Well, enough of that.
I have a software download form that asks for an email address before it
lets you download. We recently sent out a special offer email to some of
the people who had previously downloaded, so they get sent to the download
form target with a URL variable that tells the target the email address of
the person downloading. This data is stored in the download tracking table.
I totally plead not my fault on the database structure, since it was around
before me. I've convinced the client to consolidate the customer
information so the customer gets one record in the customer table that
links to the download tracking table but that is not currently how it
works. Currently, a single person can download the software via the form or
the url variable, resulting in multiple records in the database. Since it
tracks each time they download, they can get further multiples. (Then there
was this little snafu that resulted in some people going to the page and
being tracked but not being able to download until we fixed the problem and
they refreshed, for even more multiples.) We are going to send a follow-up
letter to people who have downloaded the software. The problem is that a
single email address can be in the table multiple times and we don't want
to send those people duplicate email messages. Of course, the "stop
emailing me you weenies" field and the "this address don't work" field are
in a different table that we have to join on the email address and
Well, lets just say that in trying to come up with a proper query
yesterday, I crashed CF server. Someone please hand me a fire extinguisher.
We need to get this done today and consolidating the database is going to
take several days and much planning, so we need to try to do this the bad way.
So there's this query, which generates 895 records:
<cfquery name="getdistinctemail" datasource="pkstest1">
Select distinct downloadform.email
from DownloadForm left outer join downloads
on downloadform.email = downloads.email
WHERE (downloads.IsRemoved = false or downloads.IsRemoved is null)
and (downloads.not_current = false or downloads.not_current is
null)
and len(downloadform.email) > 0
and (downloadform.referencenumber = 0 or
downloadform.referencenumber is null)
and ((DownloadForm.dateentered)
BETWEEN #createodbcdate("3/18/02")#
AND #createODBCDate("3/31/02")#)
</cfquery>
Then there's this query, which generates 946 records:
<cfquery name="getallemail" datasource="pkstest1">
Select distinct downloadform.email, downloadform.username,
downloadform.feedback, downloadform.referencenumber
from DownloadForm left outer join downloads
on downloadform.email = downloads.email
WHERE (downloads.IsRemoved = false or downloads.IsRemoved is null)
and (downloads.not_current = false or downloads.not_current is
null)
and len(downloadform.email) > 0
and (downloadform.referencenumber = 0 or
downloadform.referencenumber is null)
and ((DownloadForm.dateentered)
BETWEEN #createodbcdate("3/18/02")#
AND #createODBCDate("3/31/02")#)
</cfquery>
The only difference is that the first one gets distinct email addresses and
the second one gets distinct (all the fields we actually need). The second
one results in 51 duplicate email addresses. Every time I come up with a
plan that doesn't involve running 895 top 1 queries or making a temporary
table, I eventually realize that my plan will still result in duplicates.
I tried the last query with a group by having all those where clauses and
that's when I crashed the server. I would really like it if I could get
distinct email addresses plus the other three fields, preferably ordered so
I can select filled fields over empty ones. (Do you even want to go there?)
I'm at a loss. Does anyone have any ideas? The site is running CF Server
4.2 on NT with an Access database.
Hmmm. Thinking of storing email addresses already sent to in a list and
doing a listfind() before each email. Might work but ewwwwww.
Now available in a San Francisco Bay Area near you!
http://www.blivit.org/mr_urc/index.cfm
http://www.blivit.org/mr_urc/resume.cfm
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
