Finding Duplicates

[Fuego]

How can I find duplicates in the same table and display all records which are duplicates?
Ive com this far.


Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB;Data Source=xxxxx"
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT DISTINCT(Email),Tel FROM Relations "
objRS.Open strSQL, objConn
While objRS.EOF = False
Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.Open "Provider=SQLOLEDB;Data xxxxx"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
strSQL2 = "SELECT * FROM Relations Where Email = '" & objRS(0) & "'"
objRS2.Close
Set objRs2 = Nothing
Set objConn2 = Nothing
objRS.MoveNext
Wend
objRS.Close
Set objRs = Nothing
Set objConn = Nothing

[allida77]

You could just do this in one sql statement:

SELECT * FROM
(SELECT
COUNT(Email) AS EMAILCOUNT,Email
FROM Relations
GROUP BY Email) AS TBEMAILCOUNT
WHERE TBEMAILCOUNT.EMAILCOUNT >1

[Roelf]

or without the subquery:
SELECT COUNT(Email) AS EMAILCOUNT,Email
FROM Relations
GROUP BY Email
HAVING COUNT(Email) > 1

[Fuego]

This what i made and what I need after a lot of hard researsch and maybe usefull for you:


SELECT *
FROM Relations a
WHERE (
Email
IN
(
SELECT b.Email
FROM Relations b
GROUP BY b.Email
HAVING COUNT(1) > 1
)
)
ORDER BY Email