Left join without multiple rows from right table

Filip Eriksson picture Filip Eriksson · May 15, 2015 · Viewed 16.5k times · Source

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

Answer

Matt picture Matt · May 15, 2015

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