SQL Query Help - Multiple JOINS

Question

There were problems with this SQL query. Here's the situation:

I have three tables structured as follows:

Events -> fields ID and Name, 
Players -> fields ID and Name, 
Matches -> fields ID, EventID, Player1ID, Player2ID

I would like to execute a query that shows me a match table, but replaces EventID with Event.Name, Player1ID with Players.Name and Player2ID with Players.Name.

It's easy for me to get one player and an event using this:

SELECT 
   Players.Name as Player1, Events.Name 
FROM 
   (Matches 
INNER JOIN 
   Events ON (Matches.EventID=Events.ID))  
INNER JOIN 
   Players ON (Matches.Player1ID = Player.ID) ;

But how do I get the name Player2?

Answer 1

Add a second JOIN table to the table Players:

SELECT 
   Players.Name as Player1, Events.Name, 
   p2.Name as Player2
FROM 
   Matches 
INNER JOIN 
   Events ON Matches.EventID = Events.ID
INNER JOIN 
   Players ON Matches.Player1ID = Player.ID
INNER JOIN 
   Players p2 ON Matches.Player2ID = p2.ID;

Answer 2

, . , Players. , , from:

select m.matchid, e.name as event_name, p1.name as player1_name, p2.name as player2_name
from matches m join
     events e
     on m.eventid = e.id join
     players p1
     on m.player1 = p1.id join
     players p2
     on m.player2 = p2.id;

, .

Answer 3

SELECT p1.Name, p2.Name, Events.Name
FROM Matches
    INNER JOIN Events ON (Matches.EventID=Events.ID))  
    INNER JOIN Players p1 ON (Matches.Player1ID = p1.ID)
    INNER JOIN Players p2 ON (Matches.Player2ID = p2.ID)

Answer 4

You need to join the query again with the table Playersin the field Player2ID. Therefore, change your request to:

SELECT one.Name as Player1, two.Name as Player2, Events.Name 
FROM  
Matches INNER JOIN 
Events ON Matches.EventID=Events.ID INNER JOIN 
Players one ON Matches.Player1ID = one.ID INNER JOIN
Players two ON Matches.Player1ID = two.ID