Page 1 of 1
MySQL Query problem
Posted: Tue Jun 14, 2005 5:50 am
by frolicols
I'm trying to perform a query on two tables to produce a desired result, but am having problems.
I have two tables, called 'players' and 'challenge'. In the first table I have:
player_id, player_forename, player_surname
In the second table I have:
challenge_id, challenge_player1, challenge_player2
I want to produce a select statement for the 'challenge' table that shows the challenge_id and the players name for challenge_player1 and challenge_player2. They both reference the player_id column in 'players'.
I'm a bit of a newb, hope someone can help.
Re: MySQL Query problem
Posted: Tue Jun 14, 2005 6:08 am
by timvw
Code: Select all
SELECT challenge_id, p1.player_forname AS player1, p2.player_forname AS player2
FROM challenge AS ch
INNER JOIN players AS p1 ON (ch.challenge_player1=p1.player_id)
INNER JOIN players AS p2 ON (ch.challenge_player2=p2.player_id)
Some things you might want to consider:
- This query requires that both players are known. Challenges where one of the players is unknown/NULL will not appear in the resultset. (Need a LEFT OUTER JOIN for that)
- You can use CONCAT and friends to append the forname too...
- What exactly is the difference between forename and surname? In dutch i would think you say twice "voornaam". Where people have a surname and a family name
- Why do you prepend the table name to the column names? It only makes queries longer and adds redundancy to the column names (imho).
Posted: Tue Jun 14, 2005 8:51 am
by frolicols
Thanks that worked perfectly. I've used the CONCAT function to pull in the full name too.
Forename and Surname in english are just first and family names. E.g
Fullname = David Beckham
Forename = David
Surname = Beckham
I don't have to worry about LEFT OUTER joins, there will ALWAYS be two known players (the "Challenge" I refer to is a 3 pint challenge where 2 people have to drink 3 pints of beer through straws as quickly as possible....).
As for the table prefix in the field names, just a habit, helps me remember them!
Thanks again
Posted: Tue Jun 14, 2005 9:29 am
by timvw
Grrm @ myself

It's obvious that English is not my mother language
Was really thinking that surname was the forename.. Sorry