Basic SQL join query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Basic SQL join query help

Post by jayshields »

Hi,

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've just thought of that now, but it seems to return loads more records than the amount of records that are in the actual fixtures table, aside from the query being a total mess.

I presume theres a very simple way to do what I want to do using a JOIN of some sort.

Thanks for any ideas :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT
  `p1`.`name` AS `player 1`,
  `p2`.`name` AS `player 2`,
  `fixtures`.`score 1`,
  `fixtures`.`score 2`,
  `games`.`name` AS `game`
FROM
  `fixtures`
INNER JOIN `players` AS `p1`
  ON `p1`.`name` = `fixtures`.`player 1`
INNER JOIN `players` AS `p2`
  ON `p2`.`name` = `fixtures`.`player 2`
INNER JOIN `games`
  ON `games`.`id` = `fixtures`.`game`
ORDER BY `fixtures`.`game`
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

great. thanks alot feyd. ill test that the next opportunity i get.

thanks again.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

Ok, I made some changes to it for my field/table names, but it throws an error.

The query now looks like this:

Code: Select all

SELECT 
`p1`.`Name` AS `player 1`, 
`p2`.`Name` AS `player 2`, 
`Fixtures`.`Score 1`, 
`Fixtures`.`Score 2`, 
`Games`.`Name` AS `game` 
FROM 
`Fixtures` 
INNER JOIN 
`Pupils` AS `p1` 
ON
 `p1`.`Pupil ID` = `Fixtures`.`Player 1` 
INNER JOIN 
`Pupils` AS `p2` 
ON 
`p2`.`Pupil ID` = `Fixtures`.`Player 2` 
INNER JOIN 
`Games` 
ON 
`Games`.`Game ID` = `Fixtures`.`Game` 
ORDER BY 
`Fixtures`.`Game`
I am executing this query in Visual Basic 6.0 and I get the following error:

Code: Select all

Run-time error '3075':

Syntax error (missing operator) in query expression '`p1`.`Pupil ID` = `Fixtures`.`Player 1` INNER JOIN `Pupils` AS `p2` ON `p2`.`Pupil ID` = `Fixtures`.`Player 2` INNER JOIN `Games` ON `Games`.`Game ID` = `Fixtures`.`Game`'.
When I execute my query in MS Access I get the exact same error, this leads me to believe (inner) joins are not supported? ...because it shows the culprit section of the query right after the first ON.

Any suggestions?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Might be that aliases aren't supported in this manner. Not sure since I don't work with Access, ever.
Post Reply