List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:March 2 2011 2:49pm
Subject:RE: Query help
View as plain text  
>-----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