| 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 |