SQL join for multiple columns with ids

Question

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.

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
– sgeddes, Commented Sep 8, 2018 at 3:36

Tim Biegeleisen · Accepted Answer · 2018-09-08 03:33:46Z

3

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;

edited Sep 8, 2018 at 3:33

answered Sep 8, 2018 at 3:27

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Reynolds Over a year ago

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.

Tim Biegeleisen Over a year ago

@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.

Skoota Over a year ago

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.

Collectives™ on Stack Overflow

SQL join for multiple columns with ids

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related