SQL select with hierarchy for default values

Question

I have a table in the following format:

+---+---+--------+
| u | i | value  |   
+---+---+--------+
| 0 | 1 | Value1 |
| 0 | 2 | Value2 |
| 0 | 3 | Value3 |
| 8 | 2 | Value4 |
| 9 | 2 | Value5 |
| 9 | 3 | Value6 |
+---+---+--------+

My goal is to do a select that selects values from that user (u) for each id (i) if it exists, if not fall back to the value associated with 0. A few examples:

User ID 7 (Only default values)

Value1
Value2
Value3

User Id 8 (2 default values, 1 unique)

Value1
Value4
Value3

User Id 9 (1 default values, 2 unique)

Value1
Value5
Value6

Is this possible in a single mySQL query?

Answer 1

SELECT COALESCE( usr.value, def.value ) AS value
FROM your_table def
LEFT JOIN your_table usr ON ( usr.i = def.i AND usr.u = your_user )
WHERE def.u = 0

This selects all records where u=0 and left joins the records of your requested user.

If they exist, take the value from there, else take the default value (Using COALESCE).

Note that this will only return records where a default (u=0) exists.