Newbie query question 1

[Baukep]

Hello,

I starting to teach myself SQL, bought some books and its going good thusfar.
But some things don't work as I thought they would ;-(

I have 2 tables (MySQL):
1. Games with (amongst others) player1 and player2 in which field are the id's of the player from the players table
2. The player table with id, name, adres, etc

What I want is a query that gives me the names of the players instead of the id's.

I tried this:
SELECT `ronde` , `games`.`player1` , `players`.`name` , `games`.`player2` , `players`.`naam`
FROM `games` , `players`
WHERE `seizoen` = "2005/06"
AND `games`.`player1` = `players`.`id`
UNION SELECT `ronde` , `games`.`player1` , `players`.`naam` , `player2` , `players`.`naam`
FROM `games` , `players`
WHERE `seizoen` = "2005/06"
AND `games`.`player2` = `players`.`id`
ORDER BY 1

I do get the 2 names but always the player1's name

Any help would of course greatly be appreciated

Bauke (from Holland so English is not my native language ;-))

 

[Golom]

I don't know if you have encountered JOINs in your reading yet but, if not, look up INNER JOINS and try the following.

SELECT `ronde` 
     , `games`.`player1` 
     , `P1`.`name` 
     , `games`.`player2` 
     , `P2`.`name`

FROM (`games` 

       INNER JOIN `players` As `P1`
       ON `games`.`player1` = `P1`.`id`)

       INNER JOIN `players` As `P2`
       ON `games`.`player2` = `P2`.`id`

WHERE `seizoen` = "2005/06"

ORDER BY 1

I may not have the quotes quite right.

 

[r937]

SELECT ronde 
     , games.player1 
     , p1.name as player1_name
     , games.player2 
     , p2.name as player2_name
  FROM games 
inner
  join players as p1
    on p1.id = games.player1
inner
  join players as p2
    on p2.id = games.player2
 WHERE seizoen = '2005/06'

please, don't use those horrible backticks

r937.com | rudy.ca

 

[Baukep]

Thank you so much fro your answers!
And so fast!

I will read the chapters about joins. Your solution works fine (of course).

In the process of learning I'll be back here I guess.

Thanks again, both of you!

PS I thought MySQL needed the backticks!?!

Bauke

 

[r937]

no, MySQL does not need the backticks

p.s. in future, would you kindly post in the MySQL forum, forum436

r937.com | rudy.ca

 

[Baukep]

Thanks, and of course I will post in the MySQL forum.

Bauke