To view all your records with duplicate email addresses,
you might try something like this:
<cfquery name="select_distinct_email" datasource="dsn">
select distinct email
from mytable
</cfquery>
<cfloop query="select_distinct_email">
<cfquery name="get_records_with_duplicate_email" datasource="dsn">
select lastname, firstname, city, state
from mytable
where email = '#select_distinct_email.email#'
</cfquery>
<cfoutput query="get_offices_with_duplicate_cities">
#city_state# #office_name#<br>
<br>
</cfoutput>
</cfloop>
Check that output and see if that's what you're looking for...
Rick
> -----Original Message-----
> From: Jeff F [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 16, 2008 8:58 AM
> To: CF-Talk
> Subject: SQL Help
>
> I've got a table (MySQL) with about 20k records. I'd like to be able to get
> all fields from the
table
> with distinct email addresses. Essentially, I'm weeding out records with
> duplicate email
addresses.
>
> What I'm trying does not work:
>
> SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state
> FROM mytable
> WHERE mytable.email in (select distinct mytable.email)
>
> What am I missing??
>
> -Johny B
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312576
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
