Ad:

MySQL Discussion Thread
Unsolved
Views: 96551

Sep 13th, 2004

-1

MySQL nested query / joined query conversion help

Expand Post »

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.

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.

Similar Threads

MY PHP MySQL Function keeps returning error message: "Database Query Failed" in PHP
help with (hopefully) simple nested query in MySQL
execute one insert query and two update query, in one single button click. in VB.NET
MySql multiple table query problem.... in MySQL
Please help me out with MySQL query in MySQL

TimmyRaa

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Sep 2004

Permalink

Sep 14th, 2004

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

PoA

Reputation Points: 19

Solved Threads: 9

Posting Whiz in Training

Offline

234 posts
since Jul 2004

Permalink

Sep 14th, 2004

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Sep 2004

Permalink

Sep 15th, 2004

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

TimmyRaa

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Sep 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: mySQL foreign keys

Next Thread in MySQL Forum Timeline: design customized mysql frontend with perl or python

DaniWeb Message

Cancel Changes

User Name
Password