Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    8

    Unanswered: Help me write a "find duplicates" query

    I have posted about this before but nobody seemed able to help.

    I have a table with the fields:
    Database ID
    Forename
    Surname
    Date of Birth
    Study ID 1
    Study ID 2

    I am trying to run a "find duplicates" query to find anyone that has any of:
    The same forename and surname
    The same forename and DOB
    The same surname and DOB

    but I am just not getting the results I want .

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's the first one --
    Code:
    SELECT t.Forename
         , t.Surname
         , t.DOB
      FROM ( SELECT Forename
                  , Surname
               FROM daTable
             GROUP
                 BY Forename
                  , Surname
             HAVING COUNT(*) > 1 ) AS d
    INNER
      JOIN daTable AS t
        ON t.Forename = d.Forename
       AND t.Surname  = d.Surname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    here's the first one --
    Code:
    SELECT t.Forename
         , t.Surname
         , t.DOB
      FROM ( SELECT Forename
                  , Surname
               FROM daTable
             GROUP
                 BY Forename
                  , Surname
             HAVING COUNT(*) > 1 ) AS d
    INNER
      JOIN daTable AS t
        ON t.Forename = d.Forename
       AND t.Surname  = d.Surname
    That doesn't look like anything I have been doing . I don't know any code, I have mostly been using the wizard and trying to understand what on earth it all means. Do you just type that in the criteria box?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SNights View Post
    Do you just type that in the criteria box?
    no, you paste it in as a new query in SQL view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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