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!
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)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句