How to do a mysql select query on a table with two columns of foreign keys that relate to another table of names

Question

I have two tables, Table A has two foreign key columns (div_player1_id, div_player2_id) and Table B has a Primary key that relates to the foreign keys in Table A. I am trying to write a select query that will output the names of player1 and player 2 in one row.

wp_divisions - Div_id, div_player1_id, div_player2_id
wp_players - ID, display_name

Output = Div_Id, display_name of Player1, display_name of Player2

---

Select display_name FROM wp_players INNER Join wp_divisions where wp_players.ID = wp_divisions.div_player1_id;

This shows the Player1 names.

Select display_name FROM wp_players INNER Join wp_divisions where wp_players.ID = wp_divisions.div_player2_id;

This shows Player2 name.

I would like to have the output in a Table with:

Division    Player 1     Player 2
  1001     Joe Smith     Tom Jones

I tried following Table with two foreign keys pointing to the same column of another table

But could not adapt it to my needs

Not sure how to write the code.

Answer 1

You must join wp_divisions with wp_players twice:

select
  d.Div_id,
  p1.display_name player1,
  p2.display_name player2
from wp_divisions d
inner join wp_players p1 on p1.ID = d.div_player1_id
inner join wp_players p2 on p2.ID = d.div_player2_id

If there is a case that div_player1_id or div_player2_id is null then use left joins instead of inner joins.