Let's assume these table data
player
id name
----------
1 john
2 jane
game
id p1 p2
1 1 2
A join always selects, if possible, one tuple from one table and one tuple from another table. The effect of it is to on the fly create a new table being the merge of the two where the ON .... = ... criteria is met.
I would actually have expected your query to return no rows, since no single row in player can match both p1 and p2, unless the player has played himself.
But, with your current structure, you could of course
SELECT *
FROM games
INNER JOIN player AS pl1 ON pl1.player_id = p1
INNER JOIN player AS pl2 ON pl2.player_id = p2
Apart from it being good practice to always specify the fields you want, rather than use *, here you actually must do it so you can assign aliases to the fields having the same name. For example, both player tables aliased as pl1 and pl2 will contain player_id, name etc, so if you for example
$res = mysqli_query('SELECT pl1.id, pl1.name, pl2.id, pl2.name FROM games
INNER JOIN players ON games.p1 = players.player_id
INNER JOIN games.p2 = players.player_id';
$arr = mysql_fetch_assoc($res);
printf('<pre>%s</pre>', print_r($arr,1));
$arr = mysql_fetch_array($res);
printf('<pre>%s</pre>', print_r($arr,1));
you will in the first case get only two array elements, since you can't have two $arr[id] and two $arr[name], thus the first printf shows
Array
(
[id] => 2
[name] => Jane
)
while the second one will give you this weird mix
Array
(
[0] => 1
[id] => 2
[1] => John
[name] => Jane
[2] => 2
[3] => Jane
)
Which is why you
mysqli_query('SELECT pl1.id AS id1, pl1.name name1, pl2.id id2, pl2.name name2
FROM games
INNER JOIN players ON games.p1 = players.player_id
INNER JOIN games.p2 = players.player_id';);
$arr = mysql_fetch_assoc($res);
printf('<pre>%s</pre>', print_r($arr,1));
to get
Array
(
[id1] => 1
[name1] => John
[id2] => 2
[name2] => Jane
)
Which, to begin with, means your query won't be simpler than the one using the schema suggested by Brad. Moreover, his is normalized, while yours isn't, which leads to difficulties asking certain things. For example, write a query that shows all games in which John participated. You need to check both p1 and p2 to find this out, whereas Brad's schema
player
---------
id
name
etc
game
-------
id
entry_date
game_date
game_player
--------------
player_id
game_id
score
let's you simply
SELECT g.*, score
FROM game_player gp
INNER JOIN game g ON g.id = gp.game_id
WHERE gp.player_id = 1
If you have to keep track of which player "has the home field", add a field to game_player called "home", which can be either true or false (if boolean is supported by your db), else use 0 and 1.
When multiple fields contain data which has no inherent different meaning, just a different value, you should never stack them in the same table, but let them fill several tuples in another table. For example, in a person table, you might have one field for phone number, and one field for cell phone number, if there is a clear difference between cell and phone numbers and a person is only able to store one of each. You'd never store phone1, phone2, cell1, cell2 etc though.
And even when this difference is there, if you want to allow users to search for a person by "phone number", you'd need to either provide them with one input for cell phone numbers and another for regular phone numbers, or you'd have to be able to decide which is which by some kind of algorithm. Which means that even here you might be better off keeping a separate table
phone_numbers
-----------------
person_id INT UNSIGNED
number VARCHAR(12)
type TINYINT UNGISNED
phone_types
--------------
id TINYINT UNSIGNED
name VARCHAR(20)
Where phone_types might contain 'cell' and 'regular', or 'cell', 'home', 'work'.
In the first version, where cell and phone numbers are in the person table, you might have null values (if a person has no cell phone), which you avoid in the second (you'd have 0 related rows in phone_numbers). Some people claim you should not have a schema allowing null values in this fashion. Personally, I don't care, but you might at least view a null value as an indicator and think one more time about breaking up one relation into two separate relations, related by some kind of foreign key.
More on normalization on wikipedia
