Query help

--0016e64cbb30b7d880049d7dd074
Content-Type: text/plain; charset=ISO-8859-1

Hi

I've the following basic table

login_id
email_address
ip_address

I want to extract all records from this table in which a user has used the
same IP address but different email address to login ?

Thanks,
Neil

--0016e64cbb30b7d880049d7dd074--

Re: Query help

--bcaec5014c51fb1cae049d7e186a
Content-Type: text/plain; charset=ISO-8859-1

Hi Neil,

select
login_id,
ip_address
from
basic_table
group by
login_id,ip_address
having
count(login_id,ip_address)>1

this should work

in case you want to see also the list of emails add:

group_concat(email_address,',') as list_of_used_emails

to the select fields.


Claudio




2011/3/2 Tompkins Neil

> Hi
>
> I've the following basic table
>
> login_id
> email_address
> ip_address
>
> I want to extract all records from this table in which a user has used the
> same IP address but different email address to login ?
>
> Thanks,
> Neil
>



--
Claudio

--bcaec5014c51fb1cae049d7e186a--

RE: Query help

>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>Sent: Wednesday, March 02, 2011 6:00 AM
>To: [MySQL]
>Subject: Query help
>
>Hi
>
>I've the following basic table
>
>login_id
>email_address
>ip_address
>
>I want to extract all records from this table in which a user has used the
>same IP address but different email address to login ?
>
>Thanks,
>Neil
[JS] I haven't looked at my code lately, but I'm pretty sure that

SELECT
ip_address
FROM
basic_table
GROUP BY
ip_address
HAVING
COUNT(*) > 1;

is what you want. You don't need to group on login_id. And, as Claudio said,

SELECT
ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids

will give you the IP addresses as well.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query help

--0016e64dda780e51ea049d8154fb
Content-Type: text/plain; charset=ISO-8859-1

Thanks for the response. This is what I was after. Although, I am looking
to find out the email addresses used to login from the same IP ?

On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz wrote:

>
> >-----Original Message-----
> >From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> >Sent: Wednesday, March 02, 2011 6:00 AM
> >To: [MySQL]
> >Subject: Query help
> >
> >Hi
> >
> >I've the following basic table
> >
> >login_id
> >email_address
> >ip_address
> >
> >I want to extract all records from this table in which a user has used the
> >same IP address but different email address to login ?
> >
> >Thanks,
> >Neil
> [JS] I haven't looked at my code lately, but I'm pretty sure that
>
> SELECT
> ip_address
> FROM
> basic_table
> GROUP BY
> ip_address
> HAVING
> COUNT(*) > 1;
>
> is what you want. You don't need to group on login_id. And, as Claudio
> said,
>
> SELECT
> ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids
>
> will give you the IP addresses as well.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.the-infoshop.com
>
>
>
>
>
>
>

--0016e64dda780e51ea049d8154fb--

RE: Query help

If you want one row for each combination, you'll need either a temporary table
or a sub-query. Try this:

SELECT ip_address, login_id
FROM basic_table
JOIN
(SELECT
ip_address
FROM
basic_table
GROUP BY
ip_address
HAVING
COUNT(*) > 1) AS x
ON basic_table.ip_address = x.ip_address;

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>Sent: Wednesday, March 02, 2011 10:12 AM
>To: Jerry Schwartz
>Cc: [MySQL]
>Subject: Re: Query help
>
>Thanks for the response. This is what I was after. Although, I am looking
>to find out the email addresses used to login from the same IP ?
>
>On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz wrote:
>
>>
>> >-----Original Message-----
>> >From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>> >Sent: Wednesday, March 02, 2011 6:00 AM
>> >To: [MySQL]
>> >Subject: Query help
>> >
>> >Hi
>> >
>> >I've the following basic table
>> >
>> >login_id
>> >email_address
>> >ip_address
>> >
>> >I want to extract all records from this table in which a user has used the
>> >same IP address but different email address to login ?
>> >
>> >Thanks,
>> >Neil
>> [JS] I haven't looked at my code lately, but I'm pretty sure that
>>
>> SELECT
>> ip_address
>> FROM
>> basic_table
>> GROUP BY
>> ip_address
>> HAVING
>> COUNT(*) > 1;
>>
>> is what you want. You don't need to group on login_id. And, as Claudio
>> said,
>>
>> SELECT
>> ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids
>>
>> will give you the IP addresses as well.
>>
>> Regards,
>>
>> Jerry Schwartz
>> Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>> E-mail: jerry@gii.co.jp
>> Web site: www.the-infoshop.com
>>
>>
>>
>>
>>
>>
>>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org