Left join without multiple rows from right table

Question

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

When you have multiple duplicate users in the salary table, are there ever different salaries attached to them? — Matt, May 15 '15 at 09:45
And why do you allow duplicate rows for a user in the salaries table??? — jarlh, May 15 '15 at 09:58

score 12 · Accepted Answer · answered May 15 '15 at 09:51

12

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

answered May 15 '15 at 09:51

Matt

14,906
27
99
149

score 5 · Answer 2 · answered May 15 '15 at 09:48

5

Try this:

select U.User, U.Name, min(S.Salary)
from UserTable U
left join SalaryTable S on S.User = U.User
group by U.User, U.Name

answered May 15 '15 at 09:48

Robert

25,425
8
67
81

1

Maybe it should be max(S.Salary) – PSVSupporter May 15 '15 at 09:50
1

@PSVSupporter OP's don't care about it :) – Robert May 15 '15 at 09:51

score 3 · Answer 3 · answered May 15 '15 at 09:57

Use a derived table to get distinct rows from salaries table.

select u.userid, u.username, s.salary
from users u left join (select distinct userid, salary from salaries) s
  on u.userid = s.userid

Also, renamed tables and columns. Table names should normally end with s (since pluralis.) Columns should not.

Or, do a GROUP BY:

select u.userid, u.username, max(s.salary)
from users u left join salaries s
  on u.userid = s.userid
group by u.userid, u.username

Or skip the left join, instead do a correlated sub-query:

select u.userid, u.username, (select max(s.salary) from salaries s
                              where u.userid = s.userid)
from users

Sorry to comment on an old answer. Do you know which one of those 3 queries would have a smaller performance impact? — AJPerez, Nov 22 '18 at 18:02
@AJPerez, I don't know SQL Server that well, so it would only be a guess, better try and see for yourself. — jarlh, Nov 22 '18 at 19:44

score 3 · Answer 4 · answered May 15 '15 at 10:18

You can utilize a ROW_NUMBER to get the max (or min) salary:

SELECT *
FROM Usertable u
LEFT JOIN
 (
   select Userid, Salary,
      row_number() 
      over (partition by Userid
            order by Salary desc) as rn
   from Salarytable
 ) as s 
ON u.Userid = s.userid
AND rn = 1

And in Teradata you could apply the rn = 1filter using QUALIFY within the Derived Table:

SELECT *
FROM Usertable u
LEFT JOIN
 (
   select Userid, Salary,
      row_number() 
      over (partition by Userid
            order by Salary desc) as rn
   from Salarytable
   qualify rn = 1
 ) as s 
ON u.Userid = s.userid

score 0 · Answer 5 · answered May 15 '15 at 09:50

0

Try this

 select distinct U.User, U.Name, S.Salary
 from UserTable U
 left join SalaryTable S on S.User = U.User

answered May 15 '15 at 09:50

SimarjeetSingh Panghlia

2,200
13
14

Left join without multiple rows from right table

5 Answers5