Соединение SQL для нескольких столбцов с идентификаторами
У меня есть две таблицы SQL. В первой таблице хранится список спортсменов с их идентификаторами и именами. Например:
athlete_id | first_name | last_name
-----------|------------|----------
1 | Matthew | Reese
2 | Tiffanie | Renz
3 | Tom | Dow
так далее...
Во второй таблице хранятся записи для соревнований по треку (спринту) и идентификатор спортсмена, соревнующегося на каждой дорожке. Например:
event_id | lane1_athlete_id | lane2_athlete_id | lane3_athlete_id
---------|------------------|------------------|-----------------
1 | 1 | 15 | 24
2 | 18 | 2 | 4
3 | 78 | 50 | 3
так далее...
Мне нужно создать SQL-запрос, который вернет эту вторую таблицу, но с идентификаторами спортсменов, разрешенными в имена спортсменов. Например:
event_id | lane1_athlete | lane2_athlete | lane3_athlete
---------|---------------|---------------|--------------
1 | Matthew Reese | Lesa Allain | Nicole Spiers
2 | Emmy Bartol | Tiffanie Renz | Louise Baier
3 | Zack Bui | Norah Flagg | Tom Dow
Я предполагаю, что это связано с объединением таблиц, но я не могу понять правильный запрос. Любая помощь будет оценена по достоинству.
Ответы 1
Соедините вторую таблицу с первой трижды:
SELECT
e.event_id,
CONCAT(a1.first_name, ' ', a1.last_name) AS lane1_athlete,
CONCAT(a2.first_name, ' ', a2.last_name) AS lane2_athlete,
CONCAT(a3.first_name, ' ', a3.last_name) AS lane3_athlete
FROM events e
LEFT JOIN athletes a1
ON e.lane1_athlete_id = a1.athlete_id
LEFT JOIN athletes a2
ON e.lane2_athlete_id = a2.athlete_id
LEFT JOIN athletes a3
ON e.lane3_athlete_id = a3.athlete_id;
левые внешние соединения в этой ситуации более безопасны, чем внутренние, поскольку неверный идентификатор спортсмена приведет к исчезновению ваших строк с внутренним соединением.
@ChrisReynolds На самом деле, если таблицы спроектированы правильно, с внешними и первичными ключами, тогда не должно быть возможности даже сослаться на спортсмена в таблице событий, которой не существует. Но чтобы признать вашу точку зрения, я добавил левые соединения.
Огромное спасибо! Я не был уверен, понадобится ли повторение соединений, но, похоже, это так.
Хотя у вас есть хорошее решение, приведенное ниже, я мог бы предложить изменить вашу базу данных так, чтобы в ней было 3 таблицы вместо 2 - спортсмены, события и спортсмены (возможно, таблица 1-многие, содержащая столбец laneid). Таким образом, вам не нужно добавлять новые столбцы каждый раз, когда вводится новая полоса. Решение вашего вопроса будет другим, но я считаю, что это будет более нормализованный подход.