•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 374,006 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,847 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 52540 | Replies: 3
 |
•
•
Join Date: Sep 2004
Location: Cheltenham, UK
Posts: 20
Reputation: 
Rep Power: 4
Solved Threads: 0
Newbie Poster
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.
As you can see, it's a bit of a mix, but any help anyone can give would be great.
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.
•
•
Join Date: Jul 2004
Posts: 187
Reputation:
Rep Power: 4
Solved Threads: 2
Junior Poster
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
Location: Cheltenham, UK
Posts: 20
Reputation:
Rep Power: 4
Solved Threads: 0
Newbie Poster
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
Location: Cheltenham, UK
Posts: 20
Reputation:
Rep Power: 4
Solved Threads: 0
Newbie Poster
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
Similar Threads
- Query class problem (PHP)
- Please help me out with MySQL query (MySQL)
- to write a query (Database Design)
- Change Query Parameters (MS Access and FileMaker Pro)
- Access XP Crashes at Query Run (MS Access and FileMaker Pro)
Other Threads in the MySQL Forum
- Previous Thread: mySQL foreign keys
- Next Thread: is mysql better than dbase 4
