Showing dupes in Database

[username88]

I am having trouble with a query for my database. It is a name & address database with columns like firstname, lastname, email, etc. I am trying to show dupes in my 20,000 name database. I have written a query that shows the dupes. For example, if there are 10 email addresses as [e-mail], my output only shows that entry once. What I would like to do is output all the dupes, so I can edit or delete them. Also, another part of this is that I would like to output all dupes that match first name and last name (double column query). That is, the output would show all dupes for John Smith and all dupes for Bill Smith, not just all the Smiths.
Here is my query that shows only one instance of each email (single column query). It does not show each instance of dupes.

SELECT email, COUNT(email) AS cnt
FROM tbl_data
GROUP BY email
HAVING cnt > 1
ORDER BY cnt

PHP 5, MySQL 5

[code green]

That is because you are GROUPing by email.
This works the same as DISTINCT.
Can't see any reason to use GROUP BY

[username88]

That is because you are GROUPing by email.
This works the same as DISTINCT.
Can't see any reason to use GROUP BY

So, what would the query look like for a double column query as described above?

[code green]

I am not sure what you mean by double column query.
But if you use GROUP BY or DISTINCT you will only return unique records.
Have you got IDs in the table. You should get into the habit of using INT ids even if you see no immediate use for them.
I am confused about the names.
Do you have duplicate emails under different names?
There are varying methods to remove duplicates, a sub-query is the clearest example.
if you could give me the table construction and what result you are looking for it will help

[mwasif]

Hi username88,

Welcome to TSDN!
The following query will return all the results where the combination of first and last names are john smith and bill smith.

SELECT * from FROM tbl_data
WHERE (firstname='John' and lastname='Smith') OR (firstname='Bill' and lastname='Smith')

[username88]

I have 20,000 entries and some are duplicates. That is, the firstname and lastname are the same. I also have other fields such as email, city, state, zip, company, comments, and a few others. There are dupes such that all the fields have the same information except for the comments field. I would like to be able to display a list of dupes so that all the dupes are shown. If the dupes are shown, I can then edit the fields so that all the fields are complete.

Another point (to help describe this): The entries are sometimes added by different people and some fields are not entered. That is, one will enter john doe and leave out the state. The other will enter john doe and leave out the company. If these are displayed side by side, I can add the missing information and delete the one that is not needed.

Not sure what ID's in the table means. Can you explain that and how it will help. I will try to read about ID's in the mean time.

[username88]

SELECT *, COUNT(*)
FROM tbl_data
GROUP BY lastname, firstname
HAVING count(*) > 1

This is what I have now. If my database has 4 rows, each with a name entered that is John Doe, the above displays only ONE row with John Doe. What I want is to display ALL FOUR rows of John Doe ( and the other columns for each of the four rows). Any ideas what needs to change?

[mwasif]

Kindly use CODE tags when posting code.

If you want to show all the records, then remove GROUP BY and make simple select statement (if I am understanding correctly)

SELECT * FROM tbl_data

[code green]

As mwasif has kindly confirmed, GROUP BY is causing your problem.

Not sure what ID's in the table means. Can you explain that and how it will help. I will try to read about ID's in the mean time.

By ID I mean a numeric ID field,
The simplest way to implement this is using an extra column of type INT and the auto-increment feature.
This would help you because no matter how many duplicates you had, each record would still be unique because of the ID.
But I think what you are hoping to do is SELECT every occurrence of records with duplicated data.
You were on the right lines. Try this

SELECT * FROM tablename WHERE name IN
(SELECT name FROM tablename 
GROUP BY name 
HAVING COUNT(*) > 1) 
 

[username88]

If by ID you mean the primary key, I do have a column called "recordid". Here is what I tried:

SELECT *
FROM tbl_data
WHERE lastname IN (SELECT lastname FROM tbl_data
GROUP BY lastname
HAVING COUNT(*) > 1)
 

This times out the server after about 90 seconds. I do have about 20,000 records, and I think about 3,000 of them can be trimmed out because they are dupes. The above code does not use the primary key, so should I try to use it? If so, how?

[code green]

This times out the server after about 90 seconds

This type of query is a sub-query which are generally slow.
Try a JOIN with a temporary table.
First create a temp table

CREATE TABLE `temp` (SELECT * FROM `tbl_data` GROUP BY 'name`)

This is not strictly a temporary table. A table wil be created of unique names (or surnames or emails whichever you are looking for)
Then try this query

SELECT * 
FROM `tbl_data`
LEFT JOIN `temp` ON `temp`.`name` = `tbl_data`.`name` 
WHERE `temp`.`prodid` IS NOT NULL 

I think will pull out all duplicated names only