find duplicates problem

[nikky]

I need to find duplicates in the records of a table, but also need to see all the fields,, not just the fields that might contain duplicate information.

Here's the code I'm trying;
<CFQUERY NAME="DC" dbtype="query">
SELECT LastName, FirstName, Zip, CamperID, Zip, Email
FROM DC
WHERE LastName In (SELECT LastName FROM GetCmprs As Tmp GROUP BY LastName,FirstName,Birthday HAVING Count(*)>1 And FirstName = GetCmprs.FirstName And Zip = GetCmprs.Zip

ORDER BY LastName, FirstName, Zip

</cfquery>

 

[r937]

what database?

i don't think you can use subqueries in query-of-query

rudy

http://r937.com/

SQL Consulting

 

[webmigit]

You could do something like this, given the limitations of query-of-query..

Note the bold nocases.. I assume that you want smith and Smith to be counted as the same.. remove if you like.

I'm assuming camperID is the primary key..

<cfset dupIDs="">

  <cfset dcLN=valuelist(dc.lastname)>
  <cfset dcFN=valuelist(dc.firstname)>
  <cfset dcZip=valuelist(dc.zip)>
  <cfset dcEmail=valuelist(dc.email)>
  <cfoutput query="dc">
    (#camperID#.)
    #lastname# (#listvaluecount[b]nocase[/b](dcln,lastname)#)
    #firstname# (#listvaluecount[b]nocase[/b](dcfn,firstname)#)
    #zip# (#listvaluecount[b]nocase[/b](dcZip,zip)#)
    #email# (#listvaluecount[b]nocase[/b](dcEmail,Email)#)
    <br><br>
    [b]<cfif (listvaluecountnocase(dcln,lastname) + 
          listvaluecountnocase(dcfn,firstname) +
          listvaluecountnocase(dcZip,zip) +
          listvaluecountnocase(dcEmail,Email)) gt 4>[/b]
      <!--- Since I think you are checking for rows with duplicates of ANY data.. Then each thing should occur only once equalling a total of 4.. greater than 4 means something in the row appeared more than once --->
      <cfset dupIDs=listappend(dupIDs,camperID)>
    </cfif>
  </cfoutput>

  <cfoutput>The following rows have duplicate data: #dupIDs#</cfoutput>

You'll notice the cfif in bold above.. and how I note that I assume you're looking for any duplicates..

However if you're looking for a total row duplicate.. change the cfif to this:

<cfif listvaluecountnocase(dcln,lastname) gt 1 and 
          listvaluecountnocase(dcfn,firstname) gt 1 and
          listvaluecountnocase(dcZip,zip) gt 1 and
          listvaluecountnocase(dcEmail,Email) gt 1>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

the reason i asked "which database" is because unless it's mysql pre-4.1, you can do it all right in the sql

rudy

http://r937.com/

SQL Consulting

 

[webmigit]

Rudy,

Sorry.. I meant to add this to my post.. To await your answer first, see what you come up with.

I think highly of your skill.. you don't answer many straight CF questions but you're an sql guru, its good to have you here instead of over in sql land..

Anyway.. yeah, my code was just a backup suggestion because I can't really say how to do it in the varying versions of sql.

Go Rudy.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

thanks for the kind words, tony

here's what i would try:

select LastName
     , FirstName
     , Zip
     , CamperID
     , Email
  from GetCmprs as ZZ
 where 1 
     < ( select count(*)
           from GetCmprs
          where LastName  = ZZ.LastName
            and FirstName = ZZ.FirstName 
            and Zip       = ZZ.Zip
       )

rudy

http://r937.com/

SQL Consulting