Selecting records with the same name give duplicate results

Question

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!

Answer 1

I don't think you need JOIN for this I want to find is people who share the same name but with different id

Using Having clause to filter the name's who has more than one ID

select NAME   
from yourtable
Group by name 
having count(distinct id)> 1

Answer 2

If you want only one result you can do something like this:

 select a.name, b.name, a.id, b.id
 from staff a, staff b
 where a.name = b.name and a.id > b.id

This way, only one of the combinations between them will answer the join condition, therefore , only one will be returned

BTW - please avoid the use of implicit join syntax's(comma separated) . Use only the explicit syntax of join, like this:

 SELECT a.name, b.name, a.id, b.id
 FROM staff a
 INNER JOIN staff b
  ON(a.name = b.name and a.id > b.id)