T Lin T Lin - 5 months ago 15
SQL Question

JOIN statement to obtain MAX value

I have two tables (

user
and
I_S
) and the
I_S
table contains a column called
score
. I need to obtain the highest score and the corresponding user name from the tables.

I used a left join but I keep getting an error saying


Unknown column 'I_S.total_score' in 'field list'


This is the query I used.

SELECT MAX(interview_sessions.total_score)
FROM interview_sessions as sc
LEFT JOIN user as u on sc.user_id = u.user_id

Answer
SELECT DISTINCT users.name, I_S.score FROM users 
LEFT JOIN I_S ON I_S.user_id = users.user_id WHERE score IN (SELECT MAX(score) FROM I_S)

All users with Max score.

And i think you should write sc.total_score instead interview_sessions.total_score in your query because you use alias

Comments