query - join

15 Jul 2005 3:28 PM

Mike

I have a table 'download' with 2 fields userid,ipaddress
I would like to know the userid's that have the same ip address, how can I
get this?

sample data
ID       ip
1       1.2.3.4
2       5.6.7.8
3       192.0.0.0
4       1.2.3.4

My query should return
1   1.2.3.4
4   1.2.3.4

Thanks for your help

15 Jul 2005 3:41 PM

Alejandro Mesa

Try,

select *
from t1 as a
where exists(select * from t1 as b where b.[id] != a.[id] and b.[ip] = a.[ip])

AMB

Show quote

"Mike" wrote:

> I have a table 'download' with 2 fields userid,ipaddress
> I would like to know the userid's that have the same ip address, how can I
> get this?
>
> sample data
> ID       ip
> 1       1.2.3.4
> 2       5.6.7.8
> 3       192.0.0.0
> 4       1.2.3.4
>
> My query should return
> 1   1.2.3.4
> 4   1.2.3.4
>
> Thanks for your help