left join or subquery help me!

trouble with joins

[steveyo136]

I need help with a databse query
two databases.
Users. Users scores.

Table users

¦id ¦ name .¦Age¦
+---+-------+---+
¦ 1 ¦ steve ¦12 ¦
¦ 4 ¦ Johny ¦51 ¦

Table users scores

¦id¦ score ¦ level ¦ date .¦
-----------------------------
¦1 ¦ 1000 ¦level 1 ¦12/10/06 ¦
¦4 ¦ 4550 ¦level 5 ¦12/10/06 ¦
¦1 ¦ 1450 ¦level 8 ¦18/10/06 ¦
¦1 ¦ 5660 ¦level 7 ¦24/10/06 ¦

What I need to do is pull a list of all the user from table user and the last score posted by each user from the user_scores.
The user score database also includes other information that needs to be pulled out.
Tried to keep my explanation simple hope someone can help

[pixeltierra]

I can't think of a way to do this with pure sql. Of course I'm no super wiz either. If I had your issue, and the tables were small, I'd use a mixture of scripting and sql to get the job done.

With that in mind (and don't forget I'm not a wiz here) I see two options, one with 1 query, one with two different queries (multiple times):

1 query:
SELECT * FROM both tables ASCENDING
In a for loop push() every id into an array, and the later dates will overwrite the earlier ones because of the ASCENDING result.

2 queries:
SELECT all unique ids
in a for loop SELECT * from both tables where the id's match, DESC LIMIT 1

That will get you the last entry for each id.

I'm interested to here how this can be done in pure sql.

[mcibor]

This seems to work:

SELECT users.id, users.name, users.age, scores.score, scores.level, MAX(scores.`date.`) AS `date` FROM `users`, `scores` WHERE users.id=scores.id GROUP BY scores.id

As I tested it with the table you provided, it returned correct results.

Regards
Michal

PS. Glad to be of help, pixeltierra

[steveyo136]

mcibor your a star, that worked perfectly.

Never used the MAX sql command before! thats why I love web design/development you learn something new everyday.

thanks again!

[mcibor]

To say the truth I was thinking over this query just now, nd it may not work s desired.

Check if the rest of data (score / level) is appropriate for that row, because it may be incorrect.

I will look over this query tomorrow.

Michal

[steveyo136]

mcibor you right its not pulling out the correct value.

Its pulling the first value from the users scores table not the last.

But you have put me alot closer than I have been.

[leadegroot]

I've not had any luck pulling out the data associated with a max/min record - I've found the easiest solution is to pull all the records (that fit) and then loop in PHP and only make use of the records that change.
:(
Max/min (and the other groupers) only seem to be appropriate when it is only the max, min, etc that you want.

[FalseDawn]

SELECT U.name, (SELECT US.score FROM users_scores US WHERE US.id=U.id AND US.date=(SELECT MAX(date) FROM users_scores US2 WHERE US2.id=U.id)) FROM users U

You didn't say what DB you are using, so the above syntax max not be universal (works on mySQL)

[steveyo136]

FalseDawn how do I expand this query,
I've tried to pull the extra information from the user score table ie score and level, but if I add these to the query it starts to return all the rows from the users table.

sorry to be a pain!

[FalseDawn]

What database/version are you using?

[steveyo136]

Mysql 5.0
PHP 5.1.6

[FalseDawn]

Well, the query has to be re-jigged a bit, but this should still work:
SELECT U.name, US.score,US.level,US.date FROM users U, users_scores US WHERE US.id=U.id AND US.date=(SELECT MAX(date) FROM users_scores US2 WHERE US2.id=U.id)

[steveyo136]

Thanks FalseDawn that worked,

but,
due to my stupidity, when I've uploaded it to my server it broke. My server runs mysql 4.1.11.

I never thought about it being a different mysql version, its never been a problem before, but then I've never done complex mysql before.

I gone throught the mysql manuel, but I can't find any reason why it has broken.

[FalseDawn]

Well, mysql 4.1+ should support subqueries - check your version again, and let us know the error message.

[steveyo136]

from (phpinfo)
MySQL Support
Client API version 4.1.11

Don't know how much help this error message is,

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(date) FROM users_scores US2 WHERE US2.id

[FalseDawn]

It works fine on 4.1.21, to which I'd strongly advise you to upgrade to.