MySQL Query problem

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
frolicols
Forum Newbie
Posts: 7
Joined: Mon Jun 13, 2005 10:25 am
Location: Hertford, UK

MySQL Query problem

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: MySQL Query problem

Post 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).
frolicols
Forum Newbie
Posts: 7
Joined: Mon Jun 13, 2005 10:25 am
Location: Hertford, UK

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Grrm @ myself ;) It's obvious that English is not my mother language ;)

Was really thinking that surname was the forename.. Sorry
Post Reply