I have a table keeping client notes that can be updated and the previous note archived using an archived field. When a note is updated, the archived field is populated with the date on the old versions of the note. This allows us to see the current version (the one where archived is null) and also to review past versions (where archived is not null). For the UI, I want to show an icon for notes that have archived versions so they can access them. I know that the notes belong together because they have the same noteGroupID. Here is the simplified query:
IIF(cn2.noteID IS NOT NULL, 1, 0) AS olderVersionsAvailable
dbo.clientNotes AS cn
dbo.clientNotes AS cn2
ON cn2.noteGroupID = cn.noteGroupID
AND cn2.archived IS NOT NULL
cn.archived IS NULL
The problem is, with this code, I get multiple records if a there are multiple archived versions of the note. I could do a group to fix it, but that seems wrong for lots of reasons. How do I include BOTH olderVersionsAvailable = 1 and olderVersionsAvailable = 0 without duplicating records when there are multiple archived notes?
I am sure there must be a way to do this, but trusty google searches have not turned one up.
Any help please?