A project that I've been working on (and getting help on here) is just about complete, but I've had to develop it locally on MySQL 4.1 for it's support of nested queries - I'm rubbish with joined queries.
However, the project is going to be running on my host, who runs MySQL 3.x, which doesn't support nested queries. Therefore I'm looking for help on converting the query below, to not use any subqueries so it'll work on my host.
SELECT nickname, count( * ) , sum( score ) , max( score ) , min( score ) , avg( score ) FROM players, scoreslist WHERE players.playerid = scoreslist.playerid AND scoreslist.matchid IN ( SELECT matchid FROM matches WHERE season =2004 ) GROUP BY nickname ORDER BY avg( score ) DESC
As you can see, it's a bit of a mix, but any help anyone can give would be great.