match rows that have a certain count if aggregating on another column

I 开发者_开发知识库have a single table with these fields: Url, IP, input

I would like to retrieve all Urls (and corresponding IPs) that have a different IP with different input

example:

Url1, IP1, input1
Url2, IP2, input1
Url3, IP3, input1
Url1, IP4, input2
Url2, IP5, input2
Url3, IP3, input2
Url4, IP3, input2

I was trying to get a result like this:

Url1, IP1, input1
Url1, IP4, input2
Url2, IP2, input1
Url2, IP5, input2

as Url3 gets the same IP3 on different inputs, I don't want it in the result

also, I do not want Url4 because it's only in input2

I've search for subqueries and joins (on same table), but finally ended doing it in an external script: is there a way to do it directly in SQL

Try using GROUP BY, HAVING and sub selects:

create table your_table
(Url varchar(50),IP varchar(50), input varchar(50));


insert into your_table (Url, IP, input) values ('Url1', 'IP1', 'input1');
insert into your_table (Url, IP, input) values ('Url2', 'IP2', 'input1');
insert into your_table (Url, IP, input) values ('Url3', 'IP3', 'input1');
insert into your_table (Url, IP, input) values ('Url1', 'IP4', 'input2');
insert into your_table (Url, IP, input) values ('Url2', 'IP5', 'input2');
insert into your_table (Url, IP, input) values ('Url3', 'IP3', 'input2');



select *
from your_table
where (Url,IP) in
(
select Url, IP
from
(
select Url, IP, count(*)
from your_table
group by Url, IP
having count(*) = 1
) a
);

A GROUP BY would do

SELECT MIN(Url), IP, input
FROM   YourTable
GROUP BY
       IP, input