MySQL

MySQL nested query / joined query conversion help

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2004

Posts: 20

Reputation: TimmyRaa is an unknown quantity at this point

Solved Threads: 0

TimmyRaa

Offline

Newbie Poster

MySQL nested query / joined query conversion help

Sep 13th, 2004

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.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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.

•

Join Date: Jul 2004

Posts: 234

Reputation: PoA is an unknown quantity at this point

Solved Threads: 8

PoA

Offline

Posting Whiz in Training

Re: MySQL nested query / joined query conversion help

Sep 14th, 2004

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

•

Join Date: Sep 2004

Posts: 20

Reputation: TimmyRaa is an unknown quantity at this point

Solved Threads: 0

TimmyRaa

Offline

Newbie Poster

Re: MySQL nested query / joined query conversion help

Sep 14th, 2004

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.

•

Join Date: Sep 2004

Posts: 20

Reputation: TimmyRaa is an unknown quantity at this point

Solved Threads: 0

TimmyRaa

Offline

Newbie Poster

Re: MySQL nested query / joined query conversion help

Sep 15th, 2004

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