Two foreign keys in the parent table refer to the same child table. How to structure an INNER JOIN?

Question

For simplicity, I will describe my problem as theoretical.

Imagine that you have two tables - MATCHES and FIGHTS. "Fighters" have a list of fighters (pk_fighter_id, fighter_name), any two of which can be planned to storm each other. Matches is probably a three-field table (pk_fight_num, fk_fighter_id1, fk_fighter_id2) that tracks these pairs. Fighter1 and Fighter2 are foreign keys that refer to entries in the Fighters table.

I need to get a list of all the fights that show who is fighting with who, i.e. "23123 | Pacquaio | Marquez". How can I structure my query for this?

I would suggest 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 to mock it in Access I, it put it together, but it does not 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 close to what you want. You only need to define a unique alias for the tables, as well as a projected column with 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 an alias to the column names is only the ability to determine who is fighter1 and fighter2.

Answer 2

You must use a table alias if you attach it twice. Or the database will not know which table you had in mind with fighters . For instance:

 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 fighters connection is smoothed to f1 , the second to f2 .