1

I want to retrieve all the values from a table in which email ID are not repeated. But when I run below query the execution goes into infinite in mysql.

SELECT * FROM USERS 
WHERE EMAIL IN ( 
     SELECT EMAIL 
     FROM USERS 
     GROUP BY EMAIL 
     HAVING COUNT(1) = 1)

I don't understand why this query is going into infinite and is there any other way to get same data without inner query.

I also referred this link in which duplicate values are found link

Thanks.

Community
  • 1
  • 1
pise
  • 849
  • 6
  • 24
  • 51

5 Answers5

4

This should work as-is, but you might try to rewrite it to a Join instead, MySQL's optimizer is not that clever :-)

SELECT u.* 
FROM USERS AS u
JOIN ( SELECT EMAIL FROM USERS GROUP BY EMAIL HAVING COUNT(1) = 1) AS dt
ON u.EMAIL = dt.email
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thank You very much this solved my problem I was looking out with join because after this join I have other join with some other tables. – pise Jul 16 '14 at 07:47
3

This is one case where the MySQL extension on group by comes in handy. You can do this query as:

select u.*
from users u
group by email
having count(*) = 1;

The MySQL extension allows you to include columns in the select that are not in the group by. The values come from arbitrary (the documentation says indeterminate) matching rows. Because of the having clause, only one row matches, so the values will come from that row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Does this solve the problem ?

SELECT * FROM USERS WHERE EMAIL IN 
  ( 
    SELECT DISTINCT EMAIL
    FROM USERS 
  )
Hugh Jones
  • 2,706
  • 19
  • 30
0
SELECT DISTINCT * FROM USERS group by email

Probably the simplest answer, I tried it on my database and it works as expected.

J-Dizzle
  • 3,176
  • 6
  • 31
  • 49
0

use complex join for solve this problem.

SELECT u1.* FROM users AS u1
INNER JOIN users AS u2 ON u1.email=u2.email AND u1.ID<>u2.ID
MajidTaheri
  • 3,813
  • 6
  • 28
  • 46