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/or description could be null
  • language_id = 2 item could not exist

Tags:

Mysql

Coalesce