Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spinfold
Starting Member
2 Posts |
 Posted - 2010-08-06 : 09:44:26
|
| I have a database with which I need to find duplicate entries on a daily basis and build a HTML table containing ALL the entries - including the duplicate. The task will be set up as a cron job and the table emailed to me on a nightly basis.I can do the cron job, HTML table build, and email script, but I am having trouble working out a suitable SQL query to find the duplicates in the first place. I need to find duplicate entries which have the same time (Field: "TrainTime") AND destination (Field: "TrainDest") OR origin (Field: "TrainOrig").A normal select query would like something like this:SELECT * FROM fccstats WHERE TrainTime=[DUPLICATE] AND (TrainDest=[DUPLICATE] OR TrainOrig=[DUPLICATE])"An entry which has the same time but a different origin AND different destination is fine and should not be considered a duplicate.Can anyone help me?Thanks in advance,Stuart Pinfold |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-06 : 10:24:06
|
| [code]SELECT *FROM fccstats AS T1 JOIN fccstats AS T2 ON T2.TrainTime = T1.TrainTime AND (T2.TrainDest = T1.TrainDest OR T2.TrainOrig = T1.TrainOrig) AND T2.ID <> T1.ID -- Don't match ourself![/code] |
 |
|
|
spinfold
Starting Member
2 Posts |
Posted - 2010-08-06 : 10:46:27
|
| That's working perfectly, many thanks Kristen! |
|
|
|
|
|
|
Select duplicates based on three fields