SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot Klav's Avatar
    Join Date
    Mar 2002
    Location
    Huddersfield, UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry MySQL Query Troubles

    The problem I have is that I want to select the members from a user table where their e-mail is used more than once. If you don't understand what I'm trying to point out, then please take a look at my example below.


    Joe -- joe@funce.com
    Mark -- mark@titles.com
    Joey -- joe@funce.com
    Laura -- loz@carldo.ac.uk
    Rod -- bluesfj@hotmail.com
    Loz -- loz@carldo.ac.uk


    Now, out of the list above I'd want to select Joe, Joey, Laura and Loz because all of them are using an e-mail that is used by somebody else. Can anybody explain how to do this? Thanks.

    - Klav

  2. #2
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's name your table as 'users'. The columns: usrname and email.

    You have two choices:

    1)
    select u1.user, u1.email
    from users u1
    where exists (select 1 from users u2
    where u1.email = u2.email
    and u1.usrname != u2.usrname)

    But I just don't remember if MySql accepts this sintax. I'm sure it's accepted by others DB's (Oracle, MS Sql Server etc).

    2)
    First, this select:
    select email,count(*)
    from users
    group by email
    having count(*) > 1

    and for each line (fetched on $dupe_email)

    select usrname,email
    from users
    where email = $dupe_email

  3. #3
    SitePoint Zealot Klav's Avatar
    Join Date
    Mar 2002
    Location
    Huddersfield, UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers man, they worked a charm.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •