select all users whose emails belong to two or more users

[yawnmoth]

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?

[strawberry]

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

[Bill Karwin]

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.

[yawnmoth]

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! :)