T Lin T Lin - 1 year ago 71
SQL Question

JOIN statement to obtain MAX value

I have two tables (

) and the
table contains a column called
. 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 Source
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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download