CODEWITHSUNDEEP
sqlsqlite
Ofek
Ofek

Reputation: 404

SQLite select query if inner join query doesn't exists

I have two tables, one for all the foods which contains food id, default name and other foods values.

Another table is for the food name translations which contains food id, language id, translation.

What i want to do is join between these tables by the food id to get the translation for food id = 5 for example and language id = 1 which is Spanish for example. and i do it by:

SELECT * 
FROM Foods 
INNER JOIN FoodNameTranslations USING(Food_ID) 
WHERE Food_ID = 5 
AND Language_ID = 1

Now what if the the 'FoodNameTranslations' table doesn't have a translation for Food_ID 5 and Language 1? then i want to simply get the food row with food id = 5 from the 'Foods' table (that contains the default name).

How can i make one query that does this? thanks!

Upvotes: 0

Views: 1236

Answers (2)

Tomalak
Tomalak

Reputation: 338248

You would do a LEFT JOIN and put the language ID into the join condition.

SELECT
    COALESCE(t.TranslatedName, f.DefaultName) FoodName 
FROM 
    Foods f
    LEFT JOIN FoodNameTranslations t ON t.Food_ID = f.Food_ID AND t.Language_ID = 1
WHERE
    f.Food_ID = 5

Upvotes: 2

Eduardo Souza
Eduardo Souza

Reputation: 1

You cando do this by changing the JOIN condition:

INNER JOIN gets all records that are common between both tables based on the supplied ON clause.

LEFT JOIN gets all records from the LEFT linked and the related record from the right table ,but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.

RIGHT JOIN is like the above but gets all records in the RIGHT table.

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.

Try using LEFT JOIN instead INNER JOIN, you query will be like this: SELECT * FROM Foods LEFT JOIN FoodNameTranslations USING(Food_ID) WHERE Food_ID = 5 AND Language_ID = 1

Upvotes: 0

Related Questions