Identifying Double Entry

[e1seix]

This to me sounds like something that should be so simple, yet I can't figure it out for the life of me. How would you write a script to highlight duplicate entries in your SQL database.

 

If I have two or more entries which I would consider duplicates because of them having the same information in the columns ID and NAME.

 

How would I go about displaying that?

 

I have thought about using GROUP BY and HAVING COUNT but I get really confused. Anyone get me up onto the first step of the ladder?

 

Cheers x

[craygo]

I got this SQL statement from microsoft access. Finally something worked from there.

<?php

$sql = "SELECT ID, NAME 
FROM table
WHERE (((ID) IN (SELECT ID FROM table as Tmp GROUP BY ID, NAME HAVING count(*)>1 AND NAME = table.NAME)))
ORDER BY ID, NAME";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($res)){
echo $r['ID']." ".$r['NAME']."<br />";
}
?>

 

Substitute table for your table name

 

Ray

[Barand]

simpler

 

SELECT id, name, COUNT(*) as total

FROM tablename

GROUP BY id, name

HAVING total > 1