Skip to content
Advertisement

List only repeating names

| personid | first | last | section |
| 1        | Jon   | A    | y3      |
| 2        | Bob   | Z    | t6      |
| 3        | Pat   | G    | h4      |
| 4        | Ron   | Z    | u3      |
| 5        | Sam   | D    | y3      |
| 6        | Sam   | D    | u3      |
| 7        | Pam   | F    | h4      |

I want to isolate all the repeat names, despite the other columns, like this:

| personid | first | last | section |
| 5        | Sam   | D    | y3      |
| 6        | Sam   | D    | u3      |

This is what I came up with but I cannot get it to work:

SELECT personid, last, first, section FROM   d 01 WHERE  EXISTS 
   (SELECT * FROM d 02 WHERE  02.last = 01.last AND 02.first = 01.first )

Advertisement

Answer

You must check that the 2 rows have different ids:

SELECT d1.personid, d1.last, d1.first, d1.section 
FROM d d1 WHERE EXISTS (
  SELECT * 
  FROM d d2 
  WHERE d1.personid <> d2.personid AND d2.last = d1.last AND d2.first = d1.first 
)

Always qualify the column names with the table’s name/alias and don’t use numbers as aliases unless they are enclosed in backticks or square brackets.
See the demo.
Results:

| personid | last | first | section |
| -------- | ---- | ----- | ------- |
| 5        | D    | Sam   | y3      |
| 6        | D    | Sam   | u3      |
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement