Two foreign keys in parent table reference same child table. How to structure INNER JOIN?

Question

For simplicity's sake, I'll describe my issue as a theoretical here. Imagine you have two tables-- MATCHES and FIGHTERS. 'Fighters' has a list of fighters (pk_fighter_id, fighter_name), any two of which can be scheduled to spar each other. 'Matches' is perhaps a three-field table (pk_fight_num, fk_fighter_id1, fk_fighter_id2) which keeps track of these pairings. Fighter1 and Fighter2 are foreign keys which reference entries in the Fighters table. I need to get a list of all fights that shows who is fighting who, i.e. "23123 | Pacquaio | Marquez." How on earth do I structure my query for this though? I'd imagine something like: ``` select fk_fighter_id1, fk_fighter_id2 from matches inner join fighters on matches.fk_fighter_id1=fighters.pk_fighter_id inner join fighters on matches.fk_fighter_id2=fighters.pk_fighter_id; ``` When I tried mocking this up in Access I cobbled this together but it doesn't work: ``` SELECT matches.match_no, fighters.fighter_name, fighters.fighter_name FROM fig…

Accepted Answer

You are near to what you want. You only need to define a unique alias for the tables as well as the projected column that have the same name.

```
select  a.pk_fight_num,
        b.fighter_name firstFighter, -- <<== needed also
        c.fighter_name secondFighter -- <<== needed also
from    matches a
        inner join fighters b 
            on a.fk_fighter_id1 = b.pk_fighter_id
        inner join fighters c 
            on a.fk_fighter_id2 = c.pk_fighter_id;
```

The reason for adding alias on column names is only to be able to identify who is the fighter1 and the fighter2.

Two foreign keys in parent table reference same child table. How to structure INNER JOIN?

Solution

Problem