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 fighters 
INNER JOIN matches ON (fighters.fighter_id = matches.fighter2) AND (fighters.fighter_id = matches.fighter1);

So, any thoughts? I just don't know where to go from here.

Answer 1

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.

Answer 2

You have to alias a table if you join it twice. Or the database would not know which table you meant with fighters. For example:

select *
from matches m
inner join fighters f1 on m.fk_fighter_id1 = f1.pk_fighter_id
inner join fighters f2 on m.fk_fighter_id2 = f2.pk_fighter_id

The first join of fighters is aliased to f1, the second to f2.