I have a query that scans for people with same name but different ids. Table structure is Staff(name,id)
What I want to find is people who share the same name but with different id(they are different people).
I do happen to have two people with same name & diff id.
+---------+-----+
| NAME | ID |
+---------+-----+
| John S. | 138 |
| John S. | 491 |
+---------+-----+
so far I have
select a.name, b.name, a.id, b.id
from staff a, staff b
where a.name = b.name and a.id != b.id
But when I run this code it gives the output twice, which are
+---------+-----+
| NAME | ID |
+---------+-----+
| John S. | 138 |
| John S. | 491 |
| John S. | 491 |
| John S. | 138 |
+---------+-----+
I know why this happens because these two outputs both satisfy the checking condition, but is there anyway I can suppress ones that are already outputted? I can run a select table and WHERE ROWNUM <= 2 but that wont be the optimal case when I have more people with same names.
Thanks!