Reputation: 1085
I have a table "animal" that lists several animals each of them has an animal_id, a locale, and a name that corresponds to the locale, name can be null. The table can look like:
--------------------------------------------
id | animal_id | locale | name
--------------------------------------------
"4" | "10-3-5" | "en_US" | "dog"
"5" | "10-3-5" | "de_DE" | "hund"
"8" | "10-3-7" | "en_US" | "fox"
"9" | "10-3-7" | "de_DE" | NULL
I want to have all names for a particular locale, but some animal do not ever have a name for a particular locale (as above the name for fox is null for locale = de_DE), so the following sql:
SELECT
animal.animal_id,
animal.name
FROM animal
WHERE animal.locale = "de_DE";
gives
------------------------
animal_id | name
------------------------
"10-3-5" | "hund"
"10-3-7" | NULL
In case a name is null for a particular locale I want to retrieve the name for en_US locale, the result I want is:
------------------------
animal_id | name
------------------------
"10-3-5" | "hund"
"10-3-7" | "fox"
I tried the following query:
SELECT
animal.animal_id,
IFNULL(animal.name, (SELECT animal.name FROM animal WHERE animal.locale="en_US"))
FROM animal
WHERE animal.locale = "de_DE";
but the result is not correct and I actually do not understand it:
------------------------
animal_id | IFNULL(animal.name, (SELECT animal.name FROM animal WHERE animal.locale="en_US"))
------------------------
"10-3-5" | "hund"
"10-3-7" | "dog" <-- animal_id = 10-3-7 does not corresponds to "dog"
I also tried:
SELECT
animal.animal_id,
animal.name
FROM animal WHERE ((animal.locale = "de_DE" AND animal.name != NULL)
OR animal.locale = "en_US");
Then only name for locale equal to en_US are returned:
------------------------
animal_id | name
------------------------
"10-3-5" | "dog"
"10-3-7" | "fox"
How can I do?
Thanks in advance!
Upvotes: 2
Views: 183
Reputation: 1303
You were on the right track:
SELECT
animal.animal_id,
IFNULL(animal.name, (SELECT b.name FROM animal b WHERE b.locale="en_US" and animal.animal_id = b.animal_id))
FROM animal
WHERE animal.locale = "de_DE";
You were not filtering on the animal id on the IFNULL select. Basically, the IFNULL(..., ...) checks to see if a value is null and then will run the other query in its place. From the documentation:
The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.
Upvotes: 1