SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find Duplicates with Match Against

    We have a table with a 'name' field in our 'members' table, and it could have data such as "Mike G." all the way to "Mr. Michael Godfrey III"

    Finding duplicate records based on the 'name' field has been difficult for me to figure out. I've created a duplicate table 'members2' to help find them using MATCH AGAINST, but haven't had much luck.

    Another good search by field is 'zip'.

    If I could search for duplicates with a MATCH AGAINST on 'name' and a direct match for 'zip' I might have something.

    I've currently got a direct match on both using:
    Code:
    SELECT name, zip, count(*) cnt 
    FROM members
    GROUP BY name, zip
    HAVING cnt > 1
    ORDER BY cnt DESC;
    Any ideas?

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2012
    Posts
    118
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello ggeiger, I've tried to modify your query, and I think this just might work.
    Code:
    SELECT name, 
     COUNT(name) AS cnts
    FROM members
    GROUP BY name
    HAVING ( COUNT(name) > 1 )
    Tell me if this was any help.
    Vincentas


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •