MySQL: how to use COALESCE
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 id- Values for
name
and/ordescription
could be null language_id
= 2 item could not exist