SQL select with hierarchy for default values
I have a table in the following format:
+--开发者_Python百科-+---+--------+
| 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?
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.
精彩评论