MySQL - Odd Query

[Adrian Schneider]

This is kind of a duplication of my other thread, just more generalized so it should be easier to answer.

Here is my current test-query:
SELECT team.id, team.manager, team.managerid, stats1.matchid, team.batsmanid1, stats1.runs AS batsman1runs, team.batsmanid2, stats2.runs AS batsman2runs
FROM fc_team team, fc_player_stats stats1, fc_player_stats stats2
WHERE team.gameid =7
AND team.batsmanid1 = stats1.playerid
AND team.batsmanid2 = stats2.playerid
ORDER BY manager ASC , stats1.matchid ASC

Here are my results:
http://world-a-team.com/aj/queryresults.gif

I did a very basic query afterwords to try and figure out why the extra results:
SELECT runs
FROM `fc_player_stats`
WHERE playerid =59
AND matchid =2

The result was 8. (always the first of the many 'fake' results)

What causes these 'fake' results, and how can I filter through them. I'll also need to be able to do this on a similiar, more complex query (many more fake tables & where clauses).

[Marco van Herwaarden]

I think you will have to add a WHERE for the stat1 and stats2 gameid. But difficult to tell without the full table layout.

[Adrian Schneider]

I will try different where clauses tomorrow, thanks.

[why-not]

If you explained exactly what results you want, I could show you how to do what you are trying to do! That query you are running is not efficient, it will cause a full table scan to be done which is not always the best appoach to take!

Sonia

[Adrian Schneider]

Okay, I will PM you, thanks.

[Adrian Schneider]

Well, no response from her,

Here is my current query (whole thing)

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

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.