MySQL Select rows by key or release to select the default key

Question

I have the following language table in MySQL for selecting text in different languages.

CREATE TABLE `lang` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `group` INT(10) UNSIGNED NOT NULL, `text` VARCHAR(255) NULL DEFAULT NULL, `language` VARCHAR(10) NOT NULL DEFAULT 'def', PRIMARY KEY (`id`), UNIQUE INDEX `group_language` (`group`, `language`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT 

The table contains the following data

 id group text language 1 1 This is English def 2 2 Helo sir def 3 3 how are you? def 4 3 Wie geht es dir? de 

The group field tells me which texts belong to each other for each translation. In the above example, group "3" has the default text (English) and German translation.

Now I want to select all the texts for the German language, and if they do not exist, I want to have a backup English text for this.

Does anyone know how I can combine this into an SQL query?

Answer 1

 SELECT DISTINCT COALESCE(b.ID, a.ID) ID, COALESCE(b.`GROUP`, a.`GROUP`) `GROUP`, COALESCE(b.`text`, a.`text`) `text`, COALESCE(b.language, a.language) language FROM TableName a LEFT JOIN ( SELECT ID, `GROUP`, `text`, language FROM tableName WHERE language = 'de' ) b ON a.ID <> b.ID AND a.`GROUP` = b.`GROUP` 

• SQLFiddle Demo

Answer 2

The following query will do the LEFT JOIN of the lang table with itself, where the first set of columns will contain values ​​that correspond to the default language, and the second set will contain values ​​for the de language, if any, otherwise null. You would choose de.text , if any, or use the default text text using the standard ISNULL function.

 SELECT IFNULL(de.text, def.text) FROM lang def LEFT JOIN lang de ON def.group = de.group AND def.language = 'def' AND de.language = 'de' 

Answer 3

Yes, it seems so. Thanks to Vikdor for providing JOIN:

 SELECT def.id, def.`group`, IFNULL(curr.`text`, def.`text`) AS `text` FROM lang def LEFT JOIN lang curr ON def.`group` = curr.`group` AND def.`language` = 'def' AND curr.`language` = 'de' GROUP BY def.`group`