Как выбрать N значений по N ключам из одной и той же таблицы-справочника без нескольких LEFT JOIN?

#mysql

Есть две таблицы:

1.Справочник ref: 

+-----+-----------+
| id  |  text     |
|-----+-----------+
|  1  |  однажды  |
|  2  |  в        |
|  3  |  студеную |
|  4  |  зимнюю   |
|  5  |  пору     |
+-----+-----------+


2.Данные main: 

+------+------+------+------+-----+
| fk1  | fk2  | fk3  | fk4  | fk5 |
+------+------+------+------+-----+
|  1   |  2   |  3   |  4   |  5  |
+------+------+------+------+-----+


Чтобы получить строчку текста из записи во второй таблице, где хранятся только коды
слов можно сделать так

SELECT 
 table1.text as word1,
 table2.text as word2,
 table3.text as word3,
 table4.text as word4,
 table5.text as word5
FROM
 main
 left join ref as table1 on (fk1=table1.id)
 left join ref as table2 on (fk2=table2.id)
 left join ref as table3 on (fk3=table3.id)
 left join ref as table4 on (fk4=table4.id)
 left join ref as table5 on (fk5=table5.id);


И получить
['однажды', 'в', 'студеную', 'зимнюю', 'пору']

Но это не кажется очень правильным в моих условиях, где есть таблица объявлений main
с несколькими полями-ключами (marked_text_fk, main_text_fk), которые связаны с таблицей
"текстовок" (text_id, text_fulltext), потому что количество записей - 1 млн в первой
таблице и 0.5 млн во второй.

Пожалуйста, подскажите есть ли другие варианты запроса или разбивки данных на таблицы
с такими исходными условиями?
    

Ответы

Ответ 1

Пятикратный join - вполне нормальное решение данной задачи в такой формулировке.
(Кстати, почему вы делаете left join? Данные в таблицах что, могут быть несогласованны?)

Тут, на самом деле, странной выглядит сама схема БД. Почему в таблице main хранится
пять ссылок одного типа? Их точно никогда не придется хранить шесть штук или четыре?
Стоило бы сделать промежуточную таблицу-связку между main и ref, с полями mainid, refid
и order (1 и 3е поля входят в первичный ключ).


Ответ 2

Если сделать разумное допущение о том, что в таблице main, кроме полей с кодами слов,
есть ещё и какой-нибудь первичный ключ... скажем, id, то задачу можно решить при помощи
одного join'а и парочки хитрых трюков.

Сначала нужно размножить записи в таблице main таким образом, чтобы вместо одной
их стало пять. У каждой должен быть свой номер и свой маппинг соответствующего поля
с кодом в одно и то же поле. Затем нужно сделать обычный join со справочником. И, наконец,
нужно применить трюк с использованием case вместо обычных средств для pivot-таблиц,
которые начисто отсутствуют в mysql ввиду его полной ущербности.

SELECT
    MAX(CASE WHEN n = 1 THEN text END) AS word1,
    MAX(CASE WHEN n = 2 THEN text END) AS word2,
    MAX(CASE WHEN n = 3 THEN text END) AS word3,
    MAX(CASE WHEN n = 4 THEN text END) AS word4,
    MAX(CASE WHEN n = 5 THEN text END) AS word5
FROM (
    SELECT id, 1 AS n, fk1 AS fk FROM main UNION
    SELECT id, 2 AS n, fk2 AS fk FROM main UNION
    SELECT id, 3 AS n, fk3 AS fk FROM main UNION
    SELECT id, 4 AS n, fk4 AS fk FROM main UNION
    SELECT id, 5 AS n, fk5 AS fk FROM main) view_of_main
JOIN ref ON (view_of_main.fk = ref.id)
GROUP BY view_of_main.id;


Вложенный запрос обозначен как view_of_main не случайно. Это как бы намекает на то,
что его можно сделать вьюхой.