How is a many to many self-relationship in data modeling represented in database tables and how is the query SQL instruction?

I'm trying to query the player names according to their ID keys in their table from the two foreign keys in the match table of their self-relationship (as player plays with player).

I'm using MS Access, and the tables and fields are these:

PLAYER

player_id (PK)

player_name

MATCH

match_id (PK)

player_name_id (COMPOSITE FK from player_id)

opponent_name_id (COMPOSITE FK from player_id)

I added the match_id field only because it's important to have an id to identity the match, but the real identification is the combination of two different players, meaning a player mustn't play vs themselves, as it's each player vs everyone else.

There are players registered in the fields of both tables.

I'm not sure what the SQL instruction should be in order to bring the match_id along with the respective player names in the player_name_id and opponent_name_id fields in a query according to their foreign keys.

In order to establish the self-relationship in MS Access between the tables, the way I found was to open the same PLAYER table twice and establish the following relationships:

self-relationship established

As you can see above, the table PLAYER was mirror-opened as PLAYER_1. I had to establish the self-relationship this way because whenever I try to establish two relationships coming from player_id to two fields in the MATCH table as foreign keys, Access undoes one relationship when doing the second, so I'm also not completely sure this is correct. In the query, however, the two relationships are shown as they should as you can see below:

self-relationship in the query

After doing a lot of research for the SQL instruction, I found something called self-referencing, which seems to be the case here, but the ones I found were only used in a same table. But according to what I learned, it seems that I should address the MATCH table twice using two different aliases so that it brings the player name from the player_name_id differently than the opponent_name_id.

So I imagine the SQL instruction should look something like this:

SELECT Match.Match_ID, p.Player_name, o.Player_name FROM Match p INNER JOIN Match o ON (Player.Player_ID = Match.Player_name_ID) AND (Match.Opponent_name_ID = Player.Player_ID);

The results I got after much trial and error was that I'd either get the query right, except that instead of showing the player names as intended, I'd only see their ID numbers or I'd get only the rows of data which had players playing vs themselves, which shouldn't be allowed.

What I wanted in my query was a result like this:

expected query table

Could someone tell me if I made the self-relationship in Access properly and what the right SQL instruction is for the result I'm seeking? Thanks in advance.

Yes this is a self Join and you can open the sql pane of the designer and write it if your sql skills are up to the task. The trick is getting the designer to write it:

I set up the relationship properly with the relationships tool but when I opened the query designer I got:

Whatever your Query designer shows set up the query to dupe the players table by dragging the players table onto the design surface a second time and hooking the second table up like so:

'resulting sql
SELECT Matches.MatchID, Players.PlayerName, Matches.PlayerID, Matches.OpponentID, Players_1.PlayerName
FROM Players AS Players_1 INNER JOIN (Players INNER JOIN Matches ON Players.PlayerID = Matches.PlayerID) ON Players_1.PlayerID = Matches.OpponentID;

'output
----------------------------------------------------------------------------------------------------------
|      MatchID       | Players.PlayerName |      PlayerID      |     OpponentID     | Players_1.PlayerNa |
----------------------------------------------------------------------------------------------------------
|                  1 | Marcos             |                  1 |                  5 | Daniel             |
----------------------------------------------------------------------------------------------------------
|                  2 | John               |                  2 |                  4 | Carlos             |
----------------------------------------------------------------------------------------------------------
|                  3 | Jane               |                  3 |                  6 | George             |
----------------------------------------------------------------------------------------------------------
|                  4 | John               |                  2 |                  5 | Daniel             |
----------------------------------------------------------------------------------------------------------
|                  5 | Carlos             |                  4 |                  6 | George             |
----------------------------------------------------------------------------------------------------------

Notes:There was no need for me to finish setting up the relationship for Players_1. Also it is often easier to edit the sql than to adjust the designer for these types of queries but if the query is complicated beware. Access rewrites the query when it switches between the designer and sql panes and it can screw up the translation when the query is complicated