MySQL nested query / joined query conversion help

[TimmyRaa]

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.

[PoA]

Try the following statement.

SELECT nickname, count( * ) , sum( score ) , max( score ) , min( score ) , avg( score ) FROM players p, scoreslist s WHERE p.playerid = s.playerid AND s.matchid IN (

SELECT matchid FROM matches WHERE season =2004 ) GROUP BY nickname ORDER BY avg( score ) DESC

[TimmyRaa]

I'm no SQL expert, but that still looks like it's got a nested query in it. In fact, it's exactly the same as mine, except you've substituted players for p, and scoreslist for s.

[TimmyRaa]

Worked it out...

SELECT nickname AS name, count( * ) AS played, sum( score ) AS agg, max( score ) AS highest, min( score ) AS lowest, avg( score ) AS average FROM stats_players, stats_scoreslist, stats_matches WHERE stats_players.playerid = stats_scoreslist.playerid AND stats_scoreslist.matchid = stats_matches.matchid AND stats_matches.season = 2004 GROUP BY nickname ORDER BY average DESC , name ASC