Okay, an explanation of how this would work below.
To try and clarify, here's what I would try if I don't get an answer here on how to do this. I would try to create a query that only shows rows where SongID is unique, and then use my existing duplicate query on *that.* That's basically what I'm looking to do in the query; strip out every row that doesn't have a unique SongID before checking for duplicates.
Here's my sample data set and results:
Okay, let's say we start with a table with this data :
Code:
Title RecordingID SongID AltTitle
What 1 1
What 2 3 MASTER
What 3 3
Who 4 7
Who 5 8 DELETE
Where 6 11
Where 7 12
Why 8 14
Why 9 14
Why 10 16 UNIQUE
Which 11 18
I would want the query to give me:
Code:
Title RecordingID SongID AltTitle
Who 4 7
Who 5 8 DELETE
Where 6 11
Where 7 12
Here's an explanation for why that would be the result if I'm looking for title duplicates that don't have SongID duplicates and don't have an AltTitle of "UNIQUE":
Code:
What 1 1
What 2 3 MASTER
What 3 3
The second and third "What" have the same ID, therefore they shouldn't be in the results. If we've removed those two then the first row is no longer a double of anything so it wouldn't be in there either (although if that would make the query too complex I would be fine with the first row remaining; I would just then mark it as UNIQUE to stop it from displaying).
Code:
Who 4 7
Who 5 8 DELETE
The two "Who" rows have different SongIDs so they would be part of the results.
Code:
Where 6 11
Where 7 12
Same with the "Where" rows.
Code:
Why 8 14
Why 9 14
Why 10 16 UNIQUE
As with the "What" rows, two of these have matching SongIDs so wouldn't be part of the results, and that would make the third one also not a duplicate.
Title is not a duplicate, so not part of the results.