List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:March 2 2011 4:03pm
Subject:RE: Query help
View as plain text  
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@stripped
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@stripped]
>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 <jerry@stripped> wrote:
>
>>
>> >-----Original Message-----
>> >From: Tompkins Neil [mailto:neil.tompkins@stripped]
>> >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@stripped
>> Web site: www.the-infoshop.com
>>
>>
>>
>>
>>
>>
>>



Thread
Query helpTompkins Neil2 Mar
  • Re: Query helpClaudio Nanni2 Mar
  • RE: Query helpJerry Schwartz2 Mar
    • Re: Query helpTompkins Neil2 Mar
      • RE: Query helpJerry Schwartz2 Mar