Geek Answers Handbook

Left join without multiple rows from the right table

I have two tables ( Userand Salary). I want to do left joinfrom Userto Salary. For each user I want their name and salary. If they do not have a salary, this field may be left blank. So far, the left join is all we need. But I want only one line for each user. Due to some shortcomings, there may be several salaries for one user (see the Salary Table). I only need one line for each user, which can be randomly selected (or at the top 1). How should I do it? The expected result is presented at 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
+4
source
5

User Userid, User 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

+3

:

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
+4

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

.

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

. s ( pluralis.) .

, 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

, :

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

ROW_NUMBER, ( ) :

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

Teradata rn = 1, QUALIFY :

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
0

More articles:


All Articles