I have two tables (User
and Salary
). I want to do a left join
from User
to Salary
. For each user I want their name and salary. In case they have no salary that field can be left empty. So far a left join is all we need. But I only want one row per user. Due to some defects there can be several salaries for one user (see table salary). I only want one row per user which can be selected randomly (or top 1). How do I do that? The expected output is presented in the bottom.
User Table:
User Name
1 Adam
2 Al
3 Fred
Salary Table
User Salary
1 1000
2 2000
2 2000
Expected table:
User Name Salary
1 Adam 1000
2 Al 2000
3 Fred null
Changed User
to Userid
as User
is a reserved word in SQL
SELECT u.Userid, u.Name, MAX(S.Salary)
FROM Usertable u
LEFT JOIN Salarytable s ON u.Userid = s.userid
GROUP BY u.userid, u.name
SQL Fiddle: http://sqlfiddle.com/#!6/ce4a8/1/0