MySQL:联接表以进行翻译记录 [英] Mysql: joining tables for translation records

问题描述

我有2个具有此配置的表:

I have 2 tables with this configuration:

table language('id', 'language_name', 'iso_code')
table translation('id', 'language_id', 'translated_text')

在第一个表中，我有记录:

In the first table I have records:

---------------------------------
| id | language_name | iso_code |
---------------------------------
| 1  | English       | en       |
| 2  | Espanõl       | es       |
| 3  | Français      | fr       |
---------------------------------

第二张表:

--------------------------------------
| id | language_id | translated_text |
--------------------------------------
| 1  | 1           | Good Morning    |
| 2  | 1           | How are you?    |
| 1  | 2           | Buenos dias     |
| 2  | 3           | Comment ça va?  |
--------------------------------------

所有英语文本字符串都存在，但是其他一些语言不存在.

All English text strings exist, but some of the other languages dont.

我想显示一个包含所有英文文本字符串和相应翻译的表格，例如:

I would like to show a table with ALL English text strings and corresponding translations, like:

----------------------------------------
| text_id | en           | es          |
----------------------------------------
| 1       | Good Morning | Buenos dias |
| 2       | How are you? |             |
----------------------------------------

或

-------------------------------------------
| text_id | en           | fr             |
-------------------------------------------
| 1       | Good Morning | Comment ça va? |
| 2       | How are you? |                |
-------------------------------------------

有什么想法吗?

推荐答案

只需继续对ID上的同一表进行左连接，但要用额外的列来表示其语言...

Just keep doing left joins to same table on the ID, but extra columns representing their language...

如果每次评论查询的相应列中没有值，则进行编辑以显示英语.

Edited to show English if no value in corresponding columns per comment inquiry.

select 
      eng.id,
      eng.translated_text InEnglish,
      coalesce( spn.translated_text, eng.translated_text ) InSpanish,
      coalesce( frn.translated_text, eng.translated_text )  InFrench
   from
      translation eng
         left join translation spn
            on eng.id = spn.id
            and spn.Language_ID = 2
         left join translation frn
            on eng.id = frn.id
            and spn.Language_ID = 3
   where
      eng.Language_id = 1
   order by 
      eng.id

这篇关于MySQL:联接表以进行翻译记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！