Ok, no matter how many tutorials I read, I can't grasp how JOIN's work.
If I have a table called fixtures with the following fields: player 1, player2, score 1, score 2, game. The data is entered like so: 3, 4, 2, 2, 1, respectively.
player 1 is set to 3, this refers to a player ID in the players table, where the name is stored. Eg, player 3 is ryan.
player 2 is set to 4, this also refers to a player ID in the players table. Eg, player 4 is john.
game is set to 1, this refers to a game ID in the games table, where the game name is stored. Eg, game 1 is tennis.
How would I go about fetching all the records in the fixtures table, but instead of giving me 3, 4, 2, 2, 1. It will give me the names for the players and games instead of the useless numbers?
So it should return: ryan, john, 2, 2, tennis.
I'm sure I need a JOIN, but I've thought I would need a JOIN in the past, and managed to get away with multiple WHERE clauses, my attempt at that is below:
Code: Select all
SELECT
`players`.`name` AS `player 1`,
`players`.`name` AS `player 2`,
`fixtures`.`score 1` AS `score 1`,
`fixtures`.`score 2` AS `score 2`,
`games`.`name` AS `game`
FROM
`players`,
`fixtures`,
`games`
WHERE
`player 1` = `fixtures`.`player 1` AND
`player 2` = `fixtures`.`player 2` AND
`games`.`id` = `fixtures`.`game`
ORDER BY
`fixtures`.`game`I presume theres a very simple way to do what I want to do using a JOIN of some sort.
Thanks for any ideas