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: je...@gii.co.jp
Web site: www.the-infoshop.com
>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompk...@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 <je...@gii.co.jp> wrote:
>
>>
>> >-----Original Message-----
>> >From: Tompkins Neil [mailto:neil.tompk...@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: je...@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=arch...@jab.org
