Say I have the following table:
TABLE: product
===============================================================================
| product_id | language_id | name | description |
===============================================================================
| 1 | 1 | Widget 1 | Really nice widget. Buy it now! |
-------------------------------------------------------------------------------
| 1 | 2 | Lorem 1 | |
-------------------------------------------------------------------------------
How do I query this such that it tries to give me the name
and description
where language_id
= 2, but fall back to language_id
= 1 if the column contains a NULL?
In the above example, I should get Lorem 1
for name
and Really nice widget. Buy it now!
for description
.
How about this?
SET @pid := 1, @lid := 2;
SELECT
COALESCE(name,(
SELECT name
FROM product
WHERE product_id = @pid AND description IS NOT NULL
LIMIT 1
)) name,
COALESCE(description,(
SELECT description
FROM product
WHERE product_id = @pid AND description IS NOT NULL
LIMIT 1
)) description
FROM product
WHERE product_id = @pid
AND (language_id = @lid
OR language_id = 1)
ORDER BY language_id DESC
LIMIT 1;
where:
@pid
: current product id@lid
: current language idname
and/or description
could be nulllanguage_id
= 2 item could not exist