MySQL: how to use COALESCE

43,199

Solution 1

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

Solution 2

select name, description from product
where product_id = @pid
  and name is not null
  and description is not null
  and (language_id = @lang or language_id = 1)
order by language_id desc

where @pid is the current product id and @lang is the current language id.

The first row returned will contain the current name and description.

This assumes that the row language_id = 1 will NOT contain NULL in name or description.

Share:
43,199

Related videos on Youtube

StackOverflowNewbie
Author by

StackOverflowNewbie

Updated on October 05, 2020

Comments

  • StackOverflowNewbie
    StackOverflowNewbie over 3 years

    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.

  • Ronnis
    Ronnis almost 13 years
    Dude this is really crappy thinking. Go to sleep Ronnis!
  • StackOverflowNewbie
    StackOverflowNewbie almost 13 years
    what if language=2 does not exist? No guarantee it will exist, actually.
  • StackOverflowNewbie
    StackOverflowNewbie almost 13 years
    and how does this handle NULL columns? Will this fallback to language_id=1's value?
  • Richard Schneider
    Richard Schneider almost 13 years
    Sorry, did not see the NULL requirement. I'll edit the select stmt.
  • StackOverflowNewbie
    StackOverflowNewbie almost 13 years
    NULLs are expected values of the columns.
  • StackOverflowNewbie
    StackOverflowNewbie almost 13 years
    the questions were different. The previous question dealt with modeling. Thomas provided a suggestion on how to model and query the data. I've decided to use a modeling approach that differs from Thomas' suggestion. Now, I need to be able to query this in a similar way Thomas' suggestion.
  • ymakux
    ymakux over 8 years
    Im getting all languages

Related