CODEWITHSUNDEEP
mysqlsql
pise
pise

Reputation: 865

How find values not duplicate in mysql

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.

Upvotes: 1

Views: 2830

Answers (5)

MajidTaheri
MajidTaheri

Reputation: 3983

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

Upvotes: 0

J-Dizzle
J-Dizzle

Reputation: 3191

SELECT DISTINCT * FROM USERS group by email

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

Upvotes: 0

Hugh Jones
Hugh Jones

Reputation: 2694

Does this solve the problem ?

SELECT * FROM USERS WHERE EMAIL IN 
  ( 
    SELECT DISTINCT EMAIL
    FROM USERS 
  )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 3

dnoeth
dnoeth

Reputation: 60462

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

Upvotes: 4

Related Questions