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/ordescription
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.
Related videos on Youtube
Author by
StackOverflowNewbie
Updated on October 05, 2020Comments
-
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
anddescription
wherelanguage_id
= 2, but fall back tolanguage_id
= 1 if the column contains a NULL?In the above example, I should get
Lorem 1
forname
andReally nice widget. Buy it now!
fordescription
. -
Ronnis almost 13 yearsDude this is really crappy thinking. Go to sleep Ronnis!
-
StackOverflowNewbie almost 13 yearswhat if language=2 does not exist? No guarantee it will exist, actually.
-
StackOverflowNewbie almost 13 yearsand how does this handle NULL columns? Will this fallback to language_id=1's value?
-
Richard Schneider almost 13 yearsSorry, did not see the NULL requirement. I'll edit the select stmt.
-
StackOverflowNewbie almost 13 yearsNULLs are expected values of the columns.
-
StackOverflowNewbie almost 13 yearsthe 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 over 8 yearsIm getting all languages