Finding duplicates in two fields

peteraub

Hi,
i have a table with client details. I would like to find all records where the first name and last name are the same. e.g. if John Smith was in there twice or more.

Can someone help me with a query which would achieve this.
After I have found the duplicate entries I will then do maintence on the table.

Thanks 🆒

vaaaska

"SELECT name,lastname FROM table WHERE name=lastname";

Voila! 😉

peteraub

Sorry,
I must not have made myself clear enough. I don't want to compare first name field with last name field.

What I want to do is find duplicate entries of first name and last name being the same.

e.g.

record 1 = first_name = 'Joe', last_name = 'Blow'
record 2= first_name = 'Joe', last_name = 'Blow'

would show up as a duplicate entry. I only want to find duplicates when first name and last name are the same in different records.

Hope this clarifires it a bit.

Thanks

darthmahon

Are you specifying what the firstname and lastname are?

This is what I would do, but then I'm not sure exactly how you are specifying the names.

SELECT name,lastname FROM table WHERE name="Joe" AND (lastname="Blow")

peteraub

I just used those names as examples. I want to find distinct names which are duplicates.

e.g. there are two records with the same first name and last name in the record.

I have the feeling that the DISTINCT keyword needs to be used probably in conjunction with count but I haven't as yet been able to work out a query to do this.

peteraub

Thanks guys. I have worked out how to do it now.
First of all I query the database to get the distinct names using count(*) with GROUP BY and HAVING , then I get the first name and last name as variables to use in another query to find every record which is a duplicate and then put them into a table to view.

The code is below and works fine. Maybe some one has another way of doing this.

$result = mysql_query("SELECT first_name ,last_name, count() as n FROM client_details GROUP BY first_name, last_name HAVING n > 1");
echo "<table>";
while ($row = mysql_fetch_array($result)) {
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$result2 = mysql_query("SELECT FROM client_details WHERE first_name = '$first_name' && last_name = '$last_name'");
while ($row2 = mysql_fetch_array($result2)) {
echo "<tr><td>".$row2['first_name']."</td><td>".$row2['last_name']."</td><td>".$row2['email']."</td></tr>";
}
}
echo "</table>";