Join problem 1

[brownfox]

Can anyone help me with a join query?
Table 1:"players_round1"

[img]http://www.london-net.net/images/players_round1.gif[/img]

the values for White And Black are the primary key (ID) in table 2: "newPlayers"."

[img]http://www.london-net.net/images/newPlayers.gif[/img]

I want a query that will show "players_round1" with the LastName from "newPlayers" in place of the integers that are in the columns "White" & "Black". It's driving me mad but this can't be that hard to do! Thanks in advance...

 

[PHV]

Something like this ?
SELECT R.ID, W.LastName As WhiteName, B.LastName As BlackName
FROM (players_round1 R
LEFT JOIN newPlayers W On R.White=W.ID)
LEFT JOIN newPlayers B On R.Black=B.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brownfox]

Brilliant, thanks!