Hi All,

I have a question regarding how to write the SQL syntax to draw resutls based on duplicate data. Eg. producing a recordset that have a matching data rather that opening 2 recordsets and comparing records 1 by 1.
Eg. How would i write my sql to only pull out records that contain the same email address. below the recordset would pull out ID 1 + 3

Data:
ID Name Email
1 Tim [email protected]
2 Bob [email protected]
3 tda [email protected]
4 tom [email protected]

Hope i made sense

Regards,

Tim

TDA

__________________
TDA

Here you go,

SELECT * from [table] where emailAddress = '[email protected]'

And if you only want unique records

SELECT DISTINCT * from [table] where emailAddress = '[email protected]'

"The one language all programmers understand is profanity."

Hi thanks for the reply, but you will not know the duplates and thats what i need to find out. So my recordset would show id 1 + 3 because of the email.

So its sort of a join of the same table.

Tim

TDA

I don't understand what you mean then. From the above statement you are saying that you want a result set where the email == @someValue.

From that statement it sounds like you have duplicate entries in your database and you only want to return one unique row and, in that case, you would use SELECT DISTINCT.

What exactly are you trying to do because, based on your posts, i understand it as you are only looking for 1 unique record based on an email address.

"The one language all programmers understand is profanity."

Hi Sorry if i didn't explain it correctly, but yes im trying to find duplicates. I only want to show records if there is more than 1. Eg. Say i have 600 records 2 of the records have the same email address, so i want a create a recordset that would only show the 2 records because the email address is not unique. In the data example above my recordset would display 1 & 3. From here i could create some functionality to remove one of these records so there is no duplicate.

I could acomplish this by creating a recordset like example below, but when dealing with thousands of records it would be very slow and im sure there is a better way.

strSQL = "SELECT ID, Email FROM members"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, myConn, ect...

Do While Not objRS.EOF
strSQL2 = "SELECT Email FROM members WHERE Email = '"& objRS1("Email") &"'
Set objRS2 = ect..
objRS2.Open strSQL2, ect
  If Not objRS2.EOF Then
    Response.Write objRS("Email")
  End If
objRS2.Close

objRS.MoveNext
Loop
objRS.Close

Hope i make sense

Thanks for your help

Tim

TDA

Oh I see, you want to return duplicate rows and then delete one of the dupes based on some condition. (In the future you may want to check the database for X email address so you don't have to worry about dupes)

The answer is simple enough, use a subquery and a well thoughtout where clause =]


DELETE from [table] where exists(SELECT email From [table] t WHERE t.email = [table].email AND t.pk < [table].pk)

Ok, this will remove all of the duplicate rows from your database, it will keep the oldest record though.

If Row 1 and Row 2 are dupes (based on email address) Row 2 is deleted Row 1 remains.

For this to work correctly I am assuming you have a unique (key) field in your table that you can compare against.

Lastly, in that query, wherever you see [table] replace it with the actual name of your table.

hth.

--EDIT
Sorry I forgot that you are trying to just select the duplicate emails, here you go

SELECT t.email From tmp t left join tmp tp on t.email = tp.email where t.pk < tp.pk

All you have to do is join the table on itself.

"The one language all programmers understand is profanity."

Hi thanks for sticking with me, thats exactly what i needed. Thankyou :)

TDA

No problem, glad it worked.

"The one language all programmers understand is profanity."