DaniWeb IT Discussion Community - MySQL nested query / joined query conversion help

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/index.php)

- MySQL (http://www.daniweb.com/forums/forum126.html)

- - MySQL nested query / joined query conversion help (http://www.daniweb.com/forums/thread10744.html)

TimmyRaa

Sep 13th, 2004 1:55 pm

MySQL nested query / joined query conversion help

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	Sep 14th, 2004 4:06 am

Re: MySQL nested query / joined query conversion help

Try the following statement.

Quote:

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

Sep 14th, 2004 4:07 pm

Re: MySQL nested query / joined query conversion help

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

Sep 15th, 2004 12:27 pm

Re: MySQL nested query / joined query conversion help

Worked it out...

Quote:

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

All times are GMT -4. The time now is 12:23 am.