Professional Web Applications Themes

select all users whose emails belong to two or more users - MySQL

Say I wanted to create a query to select all the users whose emails belong to two or more users. How would I go about doing this? Here's what I have, so far (and which hasn't worked): SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY email HAVING count(*) > 1)) Any ideas?...

  1. #1

    Default select all users whose emails belong to two or more users

    Say I wanted to create a query to select all the users whose emails
    belong to two or more users. How would I go about doing this?

    Here's what I have, so far (and which hasn't worked):

    SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY
    email HAVING count(*) > 1))

    Any ideas?

    yawnmoth Guest

  2. #2

    Default Re: select all users whose emails belong to two or more users


    yawnmoth wrote:
    > Say I wanted to create a query to select all the users whose emails
    > belong to two or more users. How would I go about doing this?
    >
    > Here's what I have, so far (and which hasn't worked):
    >
    > SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY
    > email HAVING count(*) > 1))
    >
    > Any ideas?
    untested

    SELECT t1.id, t1.username
    FROM (

    SELECT DISTINCT email, count( email )
    FROM users
    GROUP BY email
    HAVING count( email ) >1
    )t2
    LEFT JOIN users t1 ON t1.email = t2.email

    strawberry Guest

  3. #3

    Default Re: select all users whose emails belong to two or more users

    yawnmoth wrote:
    > Say I wanted to create a query to select all the users whose emails
    > belong to two or more users. How would I go about doing this?
    Here's one possible solution:

    SELECT DISTINCT u1.*
    FROM users AS u1 JOIN users AS u2
    ON u1.email = u2.email AND u1.userid <> u2.userid;

    Assuming userid is a primary key, or otherwise unique column.

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: select all users whose emails belong to two or more users


    strawberry wrote:
    > yawnmoth wrote:
    > > Say I wanted to create a query to select all the users whose emails
    > > belong to two or more users. How would I go about doing this?
    > >
    > > Here's what I have, so far (and which hasn't worked):
    > >
    > > SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY
    > > email HAVING count(*) > 1))
    > >
    > > Any ideas?
    >
    > untested
    >
    > SELECT t1.id, t1.username
    > FROM (
    >
    > SELECT DISTINCT email, count( email )
    > FROM users
    > GROUP BY email
    > HAVING count( email ) >1
    > )t2
    > LEFT JOIN users t1 ON t1.email = t2.email
    Thanks! :)

    yawnmoth Guest

Similar Threads

  1. Select user for cam: no users in list
    By Kaasie in forum Macromedia Flash Flashcom
    Replies: 1
    Last Post: May 13th, 02:10 PM
  2. License.Limit.Exceeded with 4x150 users licence andonly 300 real users
    By mm_patrick in forum Macromedia Flash Flashcom
    Replies: 6
    Last Post: November 18th, 01:30 AM
  3. License.Limit.Exceeded with 4x150 users licence and only300 real users
    By danielalamaison in forum Macromedia Flash Flashcom
    Replies: 8
    Last Post: November 14th, 08:04 PM
  4. Replies: 2
    Last Post: July 2nd, 01:48 PM
  5. Replies: 0
    Last Post: September 24th, 05:39 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139