SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Find records which have matching column values

    Hi guys!

    I'm trying to find records which have matching column values the problem being, I don't know what those values are.

    In this instance, the values are the names of delegates ("John Smith", "Anna Jones" et cetera), and I'm wanting to gather those records where a delegate has added more than one.

    Does that make sense?

    For the life of me, I just can't think of how to express this as a query, so I'm hoping you guys can assist!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    To find the duplicate names you can use GROUP BY and HAVING:
    Code:
    SELECT
       name
    FROM table
    GROUP BY name
    HAVING COUNT(*) > 1

  3. #3
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    To find the duplicate names you can use GROUP BY and HAVING:
    Code:
    SELECT
       name
    FROM table
    GROUP BY name
    HAVING COUNT(*) > 1
    Hi and thanks for the reply!

    That's selecting the delegates, but only one of the record, but I'd need to select every record added by each of the delegates.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    It doesn't even select a whole record, just the names that occur more than once. Join the result to the original table (on name) and you'll have your rows:
    Code:
    SELECT 
      ....
    FROM table AS t1
    INNER JOIN
      (SELECT
          name AS dupname
       FROM table
       GROUP BY name
       HAVING COUNT(*) > 1
      ) AS t2
    ON t1.name = t2.dupname

  5. #5
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    It doesn't even select a whole record, just the names that occur more than once. Join the result to the original table (on name) and you'll have your rows:
    Code:
    SELECT 
      ....
    FROM table AS t1
    INNER JOIN
      (SELECT
          name AS dupname
       FROM table
       GROUP BY name
       HAVING COUNT(*) > 1
      ) AS t2
    ON t1.name = t2.dupname
    Yes, that's working. And thanks for the assist!


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
  •