MySQL - Odd Query

[Adrian Schneider]

Well, no response from her,

Here is my current query (whole thing)
[sql]
SELECT DISTINCT team.id, team.manager, team.managerid,
team.batsmanid1, stats1.score AS batsman1score, stats1.scorebonus AS batsman1scorebonus,
team.batsmanid2, stats2.score AS batsman2score, stats2.scorebonus AS batsman2scorebonus,
team.batsmanid3, stats3.score AS batsman3score, stats3.scorebonus AS batsman3scorebonus,
team.batsmanid4, stats4.score AS batsman4score, stats4.scorebonus AS batsman4scorebonus,
team.batsmanid5, stats5.score AS batsman5score, stats5.scorebonus AS batsman5scorebonus,
team.wicketkeeper1, stats6.score AS wicketkeeper1score, stats6.scorebonus AS wicketkeeper1scorebonus,
team.allrounder1, stats7.score AS allrounder1score, stats7.scorebonus AS allrounder1scorebonus,
team.bowler1, stats8.score AS bowler1score, stats8.scorebonus AS bowler1scorebonus,
team.bowler2, stats9.score AS bowler2score, stats9.scorebonus AS bowler2scorebonus,
team.bowler3, stats10.score AS bowler3score, stats10.scorebonus AS bowler3scorebonus,
team.bowler4, stats11.score AS bowler5score, stats11.scorebonus AS bowler4scorebonus,
team.bonus1, team.bonus2, team.bonus3, team.bonus4, team.captain


FROM fc_team team, fc_player_stats stats1, fc_player_stats stats2, fc_player_stats stats3,
fc_player_stats stats4, fc_player_stats stats5, fc_player_stats stats6, fc_player_stats stats7,
fc_player_stats stats8, fc_player_stats stats9, fc_player_stats stats10, fc_player_stats stats11

WHERE team.gameid=$gameid
AND team.batsmanid1=stats1.playerid
AND stats1.gameid=$gameid
AND team.batsmanid2=stats2.playerid
AND stats2.gameid=$gameid
AND team.batsmanid3=stats3.playerid
AND stats3.gameid=$gameid
AND team.batsmanid4=stats4.playerid
AND stats4.gameid=$gameid
AND team.batsmanid5=stats5.playerid
AND stats5.gameid=$gameid
AND team.wicketkeeper1=stats6.playerid
AND stats6.gameid=$gameid
AND team.allrounder1=stats7.playerid
AND stats7.gameid=$gameid
AND team.bowler1=stats8.playerid
AND stats8.gameid=$gameid
AND team.bowler2=stats9.playerid
AND stats9.gameid=$gameid
AND team.bowler3=stats10.playerid
AND team.bowler4=stats11.playerid
AND stats11.gameid=$gameid

ORDER BY team.manager ASC, stats1.matchid ASC[/sql]

This is probably very uneffecient, could this be done using a LEFT/RIGHT/INNER JOIN?

-- If I started the whole project from scratch (or had tons of time on my hands) I would change the table structure, but it's too late for that.