0

I have 3 tables: 1 for tags, 1 for languages and 1 one for translations.

The translations table has foreign keys id columns which point to both the tags and the languages table.

In the translations table 1 tag can be associated with multiple languages, it has the following structure:

id | tag | language | translation
1    1     1          "hello"
2    1     2          "olá"
3    1     3          "bonjour"
4    2     1          "world"
5    3     1          "dog"

What I wan't is a way to join the 3 tables but if a tag isn't associated with all the languages, I want the translation and other fields to appear as null: I've already tried all possible combinations of join.

In the example's case there's a max of 3 languages, tag 2 and 3 are only related with one language.

I'm using MySQL.

Here's the code for the tables:

CREATE TABLE `tags` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tag` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'ascii_bin',
    `isActive` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
    `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `tag` (`tag`) USING BTREE
)
COLLATE='ascii_bin'
ENGINE=InnoDB
AUTO_INCREMENT=15
;

CREATE TABLE `languages` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `language` VARCHAR(5) NOT NULL DEFAULT '' COLLATE 'ascii_bin',
    `tag` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'ascii_bin',
    `nativeName` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
    `isActive` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
    `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `code` (`language`) USING BTREE,
    UNIQUE INDEX `name` (`name`) USING BTREE,
    UNIQUE INDEX `tag` (`tag`) USING BTREE,
    CONSTRAINT `FK_languages_tags` FOREIGN KEY (`tag`) REFERENCES `hello`.`tags` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9
;

CREATE TABLE `translations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tag` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1',
    `language` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1',
    `translation` VARCHAR(5000) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
    `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK_translations_tags` (`tag`) USING BTREE,
    INDEX `FK_translations_languages` (`language`) USING BTREE,
    CONSTRAINT `FK_translations_languages` FOREIGN KEY (`language`) REFERENCES `hello`.`languages` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT `FK_translations_tags` FOREIGN KEY (`tag`) REFERENCES `hello`.`tags` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=InnoDB
AUTO_INCREMENT=72
;

Here's what I've got so far:

SELECT
`translations`.`tag` AS `tagId`,
`tags`.`tag`,
`tags`.`isActive` AS `tagIsActive`,
`translations`.`language` AS `languageId`,
`languages`.`language`,
`languages`.`tag` AS `languageTagId`,
--
(SELECT `tag` FROM `tags` WHERE `id` = `languages`.`tag`) AS `languageTag`,
--
`languages`.`nativeName` AS `languageNativeName`,
`languages`.`isActive` AS `languageIsActive`,
`translations`.`id` AS `translationId`,
`translations`.`translation`
FROM `translations`
--
LEFT OUTER JOIN `tags`
ON `tags`.`id` = `translations`.`tag`
--
LEFT OUTER JOIN `languages`
ON `languages`.`id` = `translations`.`language`
--
ORDER BY `tags`.`tag` ASC,
`languages`.`language` ASC;

Like I said, if a tag doesn't match all the languages, I need at least the translation to appear as null or as a empty string.

João Pires
  • 927
  • 1
  • 5
  • 16
  • Does this answer your question? [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/questions/3969452/how-to-select-from-two-tables-in-mysql-even-if-not-all-rows-in-one-table-have-co) – philipxy Aug 04 '20 at 08:38
  • (Clearly,) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Aug 04 '20 at 08:39
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Aug 04 '20 at 08:39
  • @philipxy, that anwser you linked didn't help either, I've tried all sort of joins, inner, outer, left, right and was not able to get a satisfactory outcome, hence I asked here about my specific case. – João Pires Aug 04 '20 at 08:55
  • Yeah, seems you want relational division, a certain different faq than the one I thought. But if you don't give a [mre] per my comment then it's not so clear what you are trying to do. And per my other comment if you don't clearly generically say what you want then you can't properly search. – philipxy Aug 04 '20 at 09:00
  • @philipxy, I've already provided the create statements for the tables as well as the query I've got so far. – João Pires Aug 04 '20 at 09:06
  • I hadn't seen your edit. But that code is unreadable & not minimal & has not data. Re division https://stackoverflow.com/q/28939367/3404097 – philipxy Aug 04 '20 at 09:09
  • 1
    *Here's the code for the tables:* Nobody needs in full DDLs. Remove columns and indices unrelative to the question. Add sample data INSERT INTO scripts (ensure that shown desired output matches this data). And specify precise MySQL version. – Akina Aug 04 '20 at 09:35

1 Answers1

2
SELECT *
FROM tags
CROSS JOIN languages
LEFT JOIN translations ON translations.tag = tags.id
                      AND translations.language = languages.id

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25