MySQL nested query / joined query conversion help

Reply

Join Date: Sep 2004
Posts: 20
Reputation: TimmyRaa is an unknown quantity at this point 
Solved Threads: 0
TimmyRaa's Avatar
TimmyRaa TimmyRaa is offline Offline
Newbie Poster

MySQL nested query / joined query conversion help

 
0
  #1
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.

  1. SELECT nickname, count( * ) , sum( score ) , max( score ) , min( score ) , avg( score )
  2. FROM players, scoreslist
  3. WHERE players.playerid = scoreslist.playerid AND scoreslist.matchid
  4. IN (
  5.  
  6. SELECT matchid
  7. FROM matches
  8. WHERE season =2004
  9. )
  10. GROUP BY nickname
  11. 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.
Reply With Quote Quick reply to this message  
Join Date: Jul 2004
Posts: 234
Reputation: PoA is an unknown quantity at this point 
Solved Threads: 8
PoA PoA is offline Offline
Posting Whiz in Training

Re: MySQL nested query / joined query conversion help

 
0
  #2
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
Reply With Quote Quick reply to this message  
Join Date: Sep 2004
Posts: 20
Reputation: TimmyRaa is an unknown quantity at this point 
Solved Threads: 0
TimmyRaa's Avatar
TimmyRaa TimmyRaa is offline Offline
Newbie Poster

Re: MySQL nested query / joined query conversion help

 
0
  #3
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.
Reply With Quote Quick reply to this message  
Join Date: Sep 2004
Posts: 20
Reputation: TimmyRaa is an unknown quantity at this point 
Solved Threads: 0
TimmyRaa's Avatar
TimmyRaa TimmyRaa is offline Offline
Newbie Poster

Re: MySQL nested query / joined query conversion help

 
0
  #4
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
Reply With Quote Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC