Re: SQL question....

[Karl Denninger <karl(at)denninger(dot)net>]

chester c young wrote:
> > create table access (name text, address ip)
> >
> > I want to construct a SELECT statement which will return ONLY tuples 
> > containing IP and name pairs IF there is an IP that has two or more 
> > NAMEs associated with it.
> >
> >
> >     
>
> many ways:
>
> select a1.* from access a1 where exists( 
>    select 1 from access a2 where a2.name=a2.name and a1.ip!=a2.ip );
>
> select a1.*
> from    access a1
> join    access a2 using( name )
> where   a1.ip != a2.ip;
>
>   
Those will return single entries as well (which is easy to do with an 
"ORDER BY", that is computationally simpler)

What I want (and can't figure out) is a SELECT that returns ONLY tuples 
with two or more NAME entries that have the same IP.

-- Karl