CODEWITHSUNDEEP
sqliteselect
eqtèöck
eqtèöck

Reputation: 1085

Sqlite how to select other row if result for one is null

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

Answers (1)

AndrewK
AndrewK

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

Related Questions