Find records which have matching column values

Databases
1

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

To find the duplicate names you can use GROUP BY and HAVING:


SELECT
   name
FROM table
GROUP BY name
HAVING COUNT(*) > 1
3

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

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:


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

Yes, that’s working. And thanks for the assist!