Find all IP columns that occures with different Logins columns-CodePudding

I've got Postgres table with text columns - LOGIN and IP. I need to find all matches where IP occured with only different LOGIN columns.

Here is an example of input data:

LOGIN    IP
A        127.0.0.1
A        127.0.0.1
B        127.0.0.2
C        127.0.0.1 // same as A
D        127.0.0.3
D        127.0.0.3
E        127.0.0.2 // same as B

Here is what I want in output:

LOGIN    IP
A        127.0.0.1
C        127.0.0.1
B        127.0.0.2
E        127.0.0.2

Can I do it with one query?

CodePudding user response：

It looks like you need exists

select distinct Login, IP
from t
where exists (
    select * from t t2
    where t2.ip = t.ip and t2.login != t.login
)
order by IP