Finding ... non-duplicated duplicates?

[Dave C]

Not sure how to phrase this or how to construct it.

The short version: I have a table of users, but they shouldn't have identical values in a given column. I'm trying to select those that do. The records are also filtered by a value from another joined table.


The longer version:


Table 1: Users

UID final_value
1 14
2 13
3 NULL
4 12
5 10

Table 2: Users_W_Conflicts

UserID ambiguous_value
3 8
3 9
4 12
4 12
5 11
5 10
5 5


So what we have here is 5 users who have taken a quiz that normally results in a single final value.
2 of those users have unambiguous values (#1=14, #2=13), while 3 users (#3,4,5) have finished with multiple values, as recorded in the second table ,where their's multiple records per user. These users could retake the quiz and narrow their value to one.


So, user 3 currently has ambiguous results (8,9). No problem.
User 4 HAD ambiguous results (resulting in multiple records in table 2), but it seems he has retaken the quiz and narrowed his result to a single result (12), recorded in Table 1. The references to User 4 in Table 2 are now obsolete (though I do not remove them).

But there appears to be a problem with user 5. He HAS a final value too, yet has multiple records in the second table, and they're different, suggesting his results are still ambiguous. This should not be possible.

It's likely to do with my record-keeping processes, but I'd like to exaine these records to see if they are still valid user results I can use.


I want to write a query that returns only those records WHERE
Users.final_value IS NOT NULL
AND
COUNT(Users_W_Conflicts.UserID) > 1
AND
Users_W_COnflicts.ambiguous_value[n] "ARE NOT EQUAL"




This is as much of the query as I've gotten:

SELECT
UID, UserID,final_value,ambiguous_value
FROM Users
LEFT JOIN
Users_W_Conflicts
ON (Users.UID=Users_W_COnflicts.ambiguous_value)
WHERE
UID IS NOT NULL
AND
Users_W_COnflicts.ambiguous_value[n] <> Users_W_COnflicts.ambiguous_value[n];

I just don't know how to compare the ambiguous_values for a given user to see if they're really ambiguous.