kevin_nikolai (4/23/2013)
Hi Sergiy, firstly thanks to you and everyone else who responded to my post.I think I should rather have named the title of my post:
How to find duplicates based on 3 fields ? (being IDNo, Tel1, Tel3 - for Student)
Your script/sql query worked ok in some instances, but in some cases excluded some students, due to Tel3 / Tel1 / IDNo. What makes writing a query for such an issue difficult is that sometimes all 3 (IDNo, Tel1, Tel3) have values, in other cases only 2 of the 3 have values, in other only 1, in other none have a value. To add more pain to the issue, first instance of IDNo / Tel1 / Tel3 for student Jack might be identical to 2nd instance of Jack, the 3rd instance they might all differ, 4th instance some might differ.
Perhaps I should just use a cte -
Student, Tel1 for 1st query.
Then Student, Tel3 for 2nd query.
Then Student, IDNo for 3rd query.
Then join all 3 together, and hope it does the job.
u know, u are querying a single table for duplicates... i mean how more simple than that could you get?!
personally, i dont like bottle feeding people... but i think my recommendations would work...
1. joining each field (as what one poster seem to have done)
2. using CTE
3. Using the having count clause...
if none of those work then you to take a look at one of the posters signature....
dont think about what u want to do with a row but what u need to do with a column.