I've been searching through forums all morning and haven't been able to make much progress. The system I work with has a particular table that will assign two unique values to a row - we'll say ID01, and ID02. ID01 is generated by the system locally, while ID02 is generated by an external interface that connects to our system. This normally works fine, except in the scenario that the interface might be running duplicates; in this case, it generates a unique ID02 from both instances of the interface, but both have the same ID01 - resulting in a duplicate entry in the user interface.
My goal is to write a query that can be run against the database that will show me all rows on the given table that have the same ID01, but a different ID02. I started with this, as this was what most of the forum questions I could find marked as the answer to similar questions:
SELECT Count(ID01), ID01, ID02 FROM Table GROUP BY ID01, ID02 HAVING (COUNT(ID01) > 1) ORDER BY ID01
The result I get back doesn't work the way I need it to, however - it gives me a list where both ID01 and ID02 are duplicate at the same time, which our system already corrects for automatically. I need it instead to show me every row where ID01 is the same (both the first and duplicate instance or instances), but ID02 is different. If it makes any difference, this is being done in SQL Server 2008.
Any help would be much appreciated.