| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
09-05-07, 04:01
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
|
Able to do in one query?
|
|
Hi all, I have two tables
Level Table
---------------
Level_ID -- Level_Name
1-----------Beginner
2-----------Intermediate
3-----------Semi Pro
4-----------Pro
5-----------God
Score Table
---------------
User_ID -- Level_ID -- Score -- Scored_Time
1001 --------1----------57------10:00:05
1001 --------1----------18------12:00:05
1001 --------2----------86------13:20:05
1001 --------2----------104-----11:00:11
1001 --------3----------56------04:23:58
1001 --------3----------49------05:00:05
1001 --------3----------81------09:20:41
1001 --------4----------213------21:10:28
1001 --------4----------159------21:14:05
1001 --------4----------198------21:15:09
Ok, now how can I get a score board for user id 1001 like below using one query?
Score Result
---------------
Level Name -- Last Score -- Best Score
Beginner-----------18------------57
Intermediate ------86-----------104
Semi Pro-----------81----------81
Pro ---------------198----------213
God---------------NULL----------NULL
Any idea?
Thanks
|
|
09-05-07, 04:15
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
USER_ID is a reserved word!! I advise you change it 
Also, Scored_Time... What is the datatype of that?
How do you define the "Last Score"?
|
|
09-05-07, 04:27
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
|
Thanks for reply
|
|
User ID just a example actually.
score time is YYY-MM-DD HH:mm:ss
Last score means latest score saved, like last played score.
Thanks
|
|
09-05-07, 04:30
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
Also, for your info. My hosting's MYSQL version is 4.0.20...
|
|
09-05-07, 04:31
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
furthermore, what is the primary key on the score table?
Here's something to get you started
Code:
SELECT l.level_name
, Max(s.score) As 'Best Score'
FROM levels l
LEFT
JOIN scores s
ON s.level_id = l.level_id
GROUP
BY l.level_name
, l.level_id
ORDER
BY l.level_id ASC
P.S. Moved to mySQL topic
|
|
09-05-07, 04:41
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
I reckon this should do the job... Let me know!
Code:
SELECT l.level_name
, s.score As [Latest Score]
, Max(s2.score) As [Best Score]
FROM levels l
LEFT
JOIN scores s
ON s.level_id = l.level_id
AND s.score_datetime = (SELECT Max(score_datetime) FROM scores WHERE s.level_id = level_id)
LEFT
JOIN scores s2
ON s2.level_id = l.level_id
GROUP
BY l.level_name
, l.level_id
, s.score
ORDER
BY l.level_id ASC
|
|
09-05-07, 04:44
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
primary key for score table is score_id.
Yeah...you rock! I am too stupid on SQL. How about Last Score? Need to do any query?
|
|
09-05-07, 04:50
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
Tested on your reckon query. Error found. are you using sub query? I believe i read somewhere below MYSQL version 4.1 no support sub queries...is that true?
|
|
09-05-07, 04:54
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
I thought 4.1 was where subqueries were introduced...
What error(s) are you getting?
EDIT: So yes, you will not be able to run this query, damnit...
I'll get back to you...
|
|
09-05-07, 04:58
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
Haha, sorry for your time.
MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Max( logtime )
FROM gb_user_score
WHERE s.level_id = '
|
|
09-05-07, 05:12
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Quote:
|
Originally Posted by kmf165
...WHERE s.level_id = '
|
I never used that 
|
|
09-05-07, 05:20
|
|
Registered User
|
|
Join Date: Sep 2007
Posts: 9
|
|
the whole query I use :
SELECT l.level_name, s.score As 'Latest Score', Max(s2.score) As 'Best Score'
FROM gb_level AS l
LEFT JOIN gb_user_score AS s ON s.level_id = l.level_id AND s.logtime = (SELECT Max(logtime) FROM gb_user_score WHERE s.level_id = level_id)
LEFT JOIN gb_user_score s2 ON s2.level_id = l.level_id
GROUP BY l.level_name, l.level_id, s.score
ORDER BY l.level_id ASC
**But I was trying to get score of user id 1001
|
|
09-05-07, 06:33
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
george, be careful -- you cannot ORDER BY a column that isn't in the SELECT clause of a GROUP BY query
if the query does not have a GROUP BY clause, then you can
|
|
09-05-07, 06:44
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
I did not know that! It just appeared to work...
I don't see why I can't order by something that's not in the SELECT clause; don't suppose you fancy providing an example?
|
|
09-05-07, 07:04
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
that sounds like work, which requires time, of which there is a very short supply
especially since mysql will go ahead and run invalid queries anyway
for the time being, please just keep this in mind: if you want to ORDER BY something, make sure it's in the SELECT clause in all cases (you're much safer that way)
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Able to do in one query?
