checking for duplicates mysql

[kirk112]

I am using the following query which highlights all duplicate email address

[code]
SELECT c.personal_email AS personal_email, COUNT( personal_email ) AS userCount
FROM candidates AS c
GROUP BY personal_email
HAVING userCount >1
LIMIT 0 , 30
[/code]

The problem that I am having is that I want to be able to query for duplicate email address but be able to candidate_ref(primary key) for each of the records instead of the email address being grouped together

Thanks for you help!

[kirk112]

*bump*

[fenway]

I'm not sure I understand what you're trying to do... you want all of the matching candidate_ref UIDs for each e-mail address found?

[kirk112]

when I run the query it groups all the email address together,

[code]
personal_email      userCount

xxxx@xxx.com            3
bbbb@bbb.com          2
zzzz@zzz.com          2
[/code]

but what I want to know is the candiadte_ref for each of the duplicated email address
[code]
candidate_ref  personal_email   

15                  xxxx@xxx.com 
154                xxxx@xxx.com 
83                  xxxx@xxx.com 
488                bbbb@bbb.com
44                bbbb@bbb.com
56                zzzz@zzz.com
78                zzzz@zzz.com
    [/code]

Hope this makes sense

Regards

Shaun

[fenway]

You could always run a subquery (UNTESTED):

[code]
SELECT candidate_ref, personal_email
FROM candidates
WHERE personal_email IN
( SELECT c.personal_email AS personal_email, COUNT( personal_email ) AS userCount
FROM candidates AS c
GROUP BY personal_email
HAVING userCount >1
LIMIT 0 , 30 )
[/code]

[kirk112]

Hi Fenway

Thanks for your assistance, I have been playing around with the code you supplied and I keep getting

#1241 - Operand should contain 1 column(s)

I have tried every different way I can to solve this error without any success,

Any ideas?


I am using version 4.1.20 if that makes any difference?

[fenway]

Sorry, my bad... the inner query is returning two operands; try this one... I'm pretty sure you can use count() in the having clause, though it's not as pretty:

[code]
SELECT candidate_ref, personal_email
FROM candidates
WHERE personal_email IN
( SELECT c.personal_email AS personal_email
FROM candidates AS c
GROUP BY personal_email
HAVING COUNT( personal_email ) >1
LIMIT 0 , 30 )
[/code]

[kirk112]

Thanks Fenway,

Worked perfect!!  Is there any reason that the query used so much resource and takes alot of time, it not a major issues just wondering if I could do anything to make it any quicker?

(163 total, Query took 101.1717 sec)

It is searching about 2000 records

Thanks for your time!

[fenway]

Do you have an index on personal_email? Post the EXPLAIN output -- it shouldn't take more than a fraction of a second.

[kirk112]

Thanks for your assistance, below is the result for explain output.


[code]SQL querySQL query:EXPLAIN SELECT candidate_ref, personal_email
FROM candidates
WHERE personal_email
IN (

SELECT c.personal_email AS personal_email
FROM candidates AS c
GROUP BY personal_email
HAVING COUNT( personal_email ) >1
)

[ Edit ] [ Skip Explain SQL ] [ Create PHP Code ]


id  select_type   table         type  possible_keys  key       key_len  ref     rows  Extra 
1    PRIMARY    candidates       ALL        NULL      NULL      NULL     NULL    1754   Using where
2   DEPENDENT    SUBQUERY       c ALL       NULL      NULL      NULL     NULL    1754   Using temporary; Using filesort [/code]

Thanks for your help!

[fenway]

You don't have any indexes?