Page 1 of 1

Basic SQL join query help

Posted: Tue Apr 04, 2006 7:44 am
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 :)

Posted: Tue Apr 04, 2006 10:03 am
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`

Posted: Tue Apr 04, 2006 4:44 pm
by jayshields
great. thanks alot feyd. ill test that the next opportunity i get.

thanks again.

Posted: Thu Apr 06, 2006 3:56 am
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?

Posted: Thu Apr 06, 2006 9:23 am
by feyd
Might be that aliases aren't supported in this manner. Not sure since I don't work with Access, ever.