I have a table with user awards, which can be of various different types.
For example, here are the records for the qualification ID 94:
So as you can see, there are 2 users, one has records for the award type of "average", "min", "max" and "final", the other has the same but no "final" award.
What I want is to get only 1 row per user. If they have an award of type "final" I want that, otherwise I want the "average" one, I don't want "min" or "max" at all.
So as an example, here is the query with just a simple IN clause:
So based on that, what I want the result to be is for the user 34562 I want the row with the "final" award, and for the user 6256 i want the row with their "average" award, since they don't have a "final" record.
I'm sure this should be fairly simple, but i'm failing miserably this morning.
I think I should be able to select the final record, then do a UNION ALL, but I can't seem to work it out in my head. Can anyone point me in the right direction?
I should point out that whilst this is MySQL for me, it needs to be compatible with other database platforms.
An easy way would be to check if an average entry exists in the where clause:
SELECT * FROM Table t WHERE qualid = 94 and (type = 'average' AND not exist(SELECT * FROM Table t2 WHERE t.qualid=t2.qualid AND t.userid=t2.userid AND type = 'final') OR type = 'final')