[SQL] SQL question....

[Karl Denninger]

 assuming the following schema:

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.


I've not figured out how to do this; I can get a list of all IPs and 
names ordered by IP, which I could then parse with a different program 
(e.g. "Select name, address from access order by address"), but the idea 
of course is to do it with one SELECT statement and return only rows 
that have multiple names listed for a given IP.


--
Karl Denninger ([EMAIL PROTECTED])
http://www.denninger.net





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] SQL question....

[chester c young]

> 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.*
fromaccess a1
joinaccess a2 using( name )
where   a1.ip != a2.ip;




  

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] SQL question....

[Karl Denninger]

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.*
fromaccess a1
joinaccess 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

Re: [SQL] SQL question....

[Harold A. Giménez Ch.]

I think this is what you're looking for:

SELECT * FROM access
  WHERE ip IN(SELECT ip FROM access
 GROUP BY ip HAVING count(*) > 1)

On Tue, May 20, 2008 at 3:17 PM, Karl Denninger <[EMAIL PROTECTED]> wrote:

>  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.*
> fromaccess a1
> joinaccess 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
>

Re: [SQL] SQL question....

[Gurjeet Singh]

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[EMAIL PROTECTED]> wrote:

>  assuming the following schema:
>
> 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.
>
> I've not figured out how to do this; I can get a list of all IPs and names
> ordered by IP, which I could then parse with a different program (e.g.
> "Select name, address from access order by address"), but the idea of course
> is to do it with one SELECT statement and return only rows that have
> multiple names listed for a given IP.
>
>
try this:

select ip, name from access where ip in ( select ip from access group by ip
having count(name) > 2);

heven't execued it, so may need some coaxing. Let me know the results.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [SQL] SQL question....

[Karl Denninger]

Gurjeet Singh wrote:
On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[EMAIL PROTECTED] 
> wrote:


 assuming the following schema:

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.

I've not figured out how to do this; I can get a list of all IPs
and names ordered by IP, which I could then parse with a different
program (e.g. "Select name, address from access order by
address"), but the idea of course is to do it with one SELECT
statement and return only rows that have multiple names listed for
a given IP.


try this:

select ip, name from access where ip in ( select ip from access group 
by ip having count(name) > 2);


heven't execued it, so may need some coaxing. Let me know the results.

Best regards,
--

A small modification got CLOSE I can live with that set of 
results. I think.


Thanks :)




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] SQL question....

[Gurjeet Singh]

On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[EMAIL PROTECTED]> wrote:

> Gurjeet Singh wrote:
>
>> On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[EMAIL PROTECTED]> [EMAIL PROTECTED]>> wrote:
>>
>> assuming the following schema:
>>
>>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.
>>
>>I've not figured out how to do this; I can get a list of all IPs
>>and names ordered by IP, which I could then parse with a different
>>program (e.g. "Select name, address from access order by
>>address"), but the idea of course is to do it with one SELECT
>>statement and return only rows that have multiple names listed for
>>a given IP.
>>
>>
>> try this:
>>
>> select ip, name from access where ip in ( select ip from access group by
>> ip having count(name) > 2);
>>
>> heven't execued it, so may need some coaxing. Let me know the results.
>>
>> Best regards,
>> --
>>
>>  A small modification got CLOSE I can live with that set of
> results. I think.


I am glad.

Harold had posted almost identical solution one hour before I did (I had the
mail ready to be sent almost after you posted, but lost power and network
connection for about an hour).

Can you please post your modified query, for the record; we might still be
able to get you _exactly_ what you want.

Best regards,



-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [SQL] SQL question....

[Karl Denninger]

Gurjeet Singh wrote:
On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[EMAIL PROTECTED] 
> wrote:


Gurjeet Singh wrote:

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger
<[EMAIL PROTECTED] 
>> wrote:

    assuming the following schema:

   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.

   I've not figured out how to do this; I can get a list of
all IPs
   and names ordered by IP, which I could then parse with a
different
   program (e.g. "Select name, address from access order by
   address"), but the idea of course is to do it with one SELECT
   statement and return only rows that have multiple names
listed for
   a given IP.


try this:

select ip, name from access where ip in ( select ip from
access group by ip having count(name) > 2);

heven't execued it, so may need some coaxing. Let me know the
results.

Best regards,
-- 


A small modification got CLOSE I can live with that set of
results. I think.


I am glad.

Harold had posted almost identical solution one hour before I did (I 
had the mail ready to be sent almost after you posted, but lost power 
and network connection for about an hour).


Can you please post your modified query, for the record; we might 
still be able to get you _exactly_ what you want.


Best regards,



I used an "order by" and also increased the count to "> 2" because there 
are a lot of blank "name" records in there as well (but I don't want to 
select on those; as an artifact of how the system works there will 
usually be a blank name entry for most IP corresponding entries, but not 
all)


Karl Denninger ([EMAIL PROTECTED])
http://www.denninger.net