Conn Warwicker Conn Warwicker - 2 months ago 19
MySQL Question

SQL OR clause priority

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:

enter image description here

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:

enter image description here

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.

Thanks.

Answer

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')