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.
MySQL Query problem
Moderator: General Moderators
Re: MySQL Query problem
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).
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
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