0

I've got the following MySQL query:

SELECT user_id, score, time 
FROM tests_1 T
WHERE T.score = (
SELECT MAX(T2.score)
FROM tests_1 T2
WHERE T2.user_id = T.user_id
)
ORDER BY score DESC, time ASC;

How do I add the 'username' column from the 'users' table ON users.user_id = tests_1.user_id?

Bhav
  • 1,957
  • 7
  • 33
  • 66
  • First off, it looks like you are missing a `FROM tests_1 T` on line 2 of your code snippet. Is that correct? – Mark Silverberg Jul 13 '14 at 15:53
  • possible duplicate of [Selecting multiple columns/fields in MySQL subquery](http://stackoverflow.com/questions/5686271/selecting-multiple-columns-fields-in-mysql-subquery) – Mark Silverberg Jul 13 '14 at 15:53

3 Answers3

1

Try this:

SELECT T.user_id, U.username, T.score, T.time 
FROM tests_1 T
JOIN users U on U.user_id = T.user_id
WHERE T.score = (
SELECT MAX(T2.score)
FROM tests_1 T2
WHERE T2.user_id = T.user_id
)
ORDER BY T.score DESC, T.time ASC;
Jay Kazama
  • 3,167
  • 2
  • 19
  • 25
0

Just join it.

SELECT T.user_id, T.score, T.time, u.username
FROM tests_1 T
JOIN users u ON u.user_id = T.user_id
WHERE T.score = (
SELECT MAX(T2.score)
FROM tests_1 T2
WHERE T2.user_id = T.user_id
)
ORDER BY T.score DESC, T.time ASC;
wvdz
  • 16,251
  • 4
  • 53
  • 90
0
SELECT user_id, score, time, U.username
FROM tests_1 T JOIN users U
    ON T.user_id = U.user_id
WHERE T.score = (
    SELECT MAX(T2.score)
    FROM tests_1 T2
    WHERE T2.user_id = T.user_id
)
ORDER BY score DESC, time ASC;
Morad
  • 734
  • 5
  • 14