How to filter columns which contains something 2 times max and one of them contains something?

I have a table like this;

user - item - ip
user1 - item1 - ip1
user2 - item2 - ip1
user3 - item3 - ip1

user1 - item1 - ip2
user2 - item2 - ip2

I want to list every ip2 which belongs to any user and contains item2. Also this ip2 should be exists in 2 columns max. If it exists more than 2 times such as in ip1 then don’t choose it.

How can I do this?

Answer

Group by ip and set the conditions in the having clause:

select ip
from tablename
group by ip
having sum(item = 'item2') > 0 and count(*) <= 2 and sum(user = 'user1') > 0;