SQL join for multiple columns with ids
SQL join for multiple columns with ids
I have two SQL tables. The first table stores a list of athletes with their id and name. For example:
athlete_id | first_name | last_name
-----------|------------|----------
1 | Matthew | Reese
2 | Tiffanie | Renz
3 | Tom | Dow
etc...
The second table stores entries for a track (sprint) event, and the id of the athlete competing in each lane. For example:
event_id | lane1_athlete_id | lane2_athlete_id | lane3_athlete_id
---------|------------------|------------------|-----------------
1 | 1 | 15 | 24
2 | 18 | 2 | 4
3 | 78 | 50 | 3
etc...
I need to create an SQL query which will return that second table, but with the athlete ids resolved to the athlete names. For example:
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
I imagine this involves a table join, but I can't get my head around the correct query. Any help would be appreciated.
1 Answer
1
Join the second table to the first one, three times:
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;
left outer joins are safer than inner joins in this situation as an invalid athlete id will cause your rows to disappear with an inner join.
– Chris Reynolds
Sep 8 '18 at 3:32
@ChrisReynolds Actually if the tables are designed properly, with foreign and primary keys, then it should not even be possible to refer to an athlete in the events table which does not exist. But to concede to your point, I have added left joins.
– Tim Biegeleisen
Sep 8 '18 at 3:35
Thanks so much! I wasn't sure if it was going to need the repetition of the joins, but that seems to be the case.
– Skoota
Sep 8 '18 at 4:07
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
While you have a good solution below, I might suggest changing your database to have 3 tables instead of 2 -- athletes, events, and eventathletes (1-many table containing a laneid column perhaps). That way you don't have to add more columns every time a new lane is introduced. The solution to your question would be different, but I believe this would be the more normalized approach.
– sgeddes
Sep 8 '18 at 3:36