Need help with a query/result

[Adrian Schneider]

Ok here are my 2 tables being used:

fc_team and fc_player

I need to display the player name of each field in team [which are just ids] (ie fc_team.batsmanid1=fc_player.id)

I tried using left join but since there would be a different 'name' field for each id it won't work.

I got the query working using
[sql]
SELECT team.batsmanid1, player1.name, team.batsmanid2, player2.name
FROM fc_team team, fc_player player1, fc_player player2
WHERE team.batsmanid1=player1.id
AND team.batsmanid2=player2.id
AND team.id=$teamid
[/sql]

Upon showing this (I just used 2 of the positions for testing), I could only get one name to display, so how would I go about doing this? Can you rename the result to something like name2 instead of having 4 names, which causes conflicts?