左联接具有默认关系 [英] Left join with default relation

问题描述

需要使用意大利语翻译查找所有单词，如果意大利语不存在，则需要使用西班牙语(默认语言). 我不能使用多个查询，并且存在条件(技术限制)

Need to find all words with their Italian translation, if Italian doesn't exist, then need with Spanish (default language). I can`t use more than one query, and where exists condition(technical limitations)

单词

id|name
-------
 1|Dog
 2|Cat

翻译

id|word_id|translation|language
-------------------------------
 1|      1|      Perro|es
 2|      1|      Cane |it
 3|      2|      Gatto|es

结果:

id|name|translation|language
 1| Dog|       Cane|it
 2| Cat|      Gatto|es

---

SELECT * FROM words LEFT JOIN translation ON words.id = translation.word_id WHERE language = 'it' OR (language = 'es' AND NOT EXISTS(SELECT * FROM translation WHERE word_id = words.id AND language = 'it'))

此代码返回我需要的所有内容，但我无法在我遇到的情况下使用存在条件的地方

This code return all I need, but I can't use where exists conditions in my situation

推荐答案

我将两次连接translations表上的words表，每种语言一次:

I'd join the words table on the translations table twice, once for each language:

SELECT    w.id, 
          w.name,
          COALESCE(it.translation, es.translation) AS translation,
          COALESCE(it.language, es.language) AS language
FROM      words w
LEFT JOIN translation it ON w.id = it.word_id AND it.language = 'it'
LEFT JOIN translation es ON w.id = es.word_id AND es.language = 'es'

这篇关于左联接具有默认关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！