-1

I have 2 tables employees(id and name) and salary(id, salary), the 1st has 4 rows and 2nd has 2 rows.

table 1            Table 2
id   Name         id    salary
1     Sue          1    10000 
2    Sarah         3     9000
3    Nick 
4    james

I want a join as follows

id   Name     Salary
1     Sue     10000
2    Sarah    No Salary
3    Nick     9000
4    james    No salary
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
LearningSQL
  • 33
  • 1
  • 4
  • 2
    So you created an account here just to ask us do your homework? In the same time you could have googled about SQL JOINs and finish the assignment. – red Mar 09 '11 at 18:46
  • How much do I get with "Bo salary"? "Bo" as in "Bonus", right? – OMG Ponies Mar 09 '11 at 18:46
  • Come on guys.. I am beginer with SQL so posted such a question.. Do not reply if you think that was too dumb.. – LearningSQL Mar 09 '11 at 18:53
  • They're saying you should have done some reading, *tried something*, then come here to ask questions if you still didn't understand. Otherwise, you risk learning Cargo Cult programming - you get the little bit that plugs in and works, but don't know *why* it works. – DaveE Mar 09 '11 at 19:12
  • @DaveE what bothered me the most was the sheer number of people answering who didn't grasp they needed an outer join. – HLGEM Mar 09 '11 at 21:41
  • @HLGEM- I understand completely. I work with folks who for years have supported a product based on SQL Server and who do not understand that all JOINs are not created equal. – DaveE Mar 10 '11 at 21:17

4 Answers4

7

To get all rows from T1 when joining to table T2 that is lacking the rows 2 and 4 you need to use a left outer join. For row 2 and 4 salary will be null.

To replace the null value with something else you can use coalesce. Coalesce returns the first nonnull argument.

Since field salary is declared as an int field and you want No Salary as output where there is no salary you need to cast the int to a varchar before using it as an argument in coalesce. 

declare @T1 table(id int, name varchar(10))
declare @T2 table(id int, salary int)

insert into @T1 values(1, 'Sue')
insert into @T1 values(2, 'Sarah')
insert into @T1 values(3, 'Nick')
insert into @T1 values(4, 'james') 

insert into @T2 values(1, 10000)
insert into @T2 values(3, 9000)

select
  T1.id,
  T1.name,
  coalesce(cast(T2.salary as varchar(10)), 'No Salary') as salary 
from @T1 as T1
  left outer join @T2 as T2
    on T1.id = T2.id

Result

id          name       salary
----------- ---------- ----------
1           Sue        10000
2           Sarah      No Salary
3           Nick       9000
4           james      No Salary
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
2
SELECT e.id, e.name , 
case
when s.salary is null then 'no salary'
else cast(s.salary as varchar)
end
FROM employees e LEFT JOIN salary s
ON e.id=s.id
order by e.id
BlackTigerX
  • 6,006
  • 7
  • 38
  • 48
0

Your goal is to list all employees, regardless of whether or not they have a listed salary, so you should be using a LEFT JOIN. An inner join would potentially list salaries for employees that no longer have a listing in the Employee table and an implicit join (I believe) would be missing rows.

Something like this should do what you need:

SELECT E.id, E.name, S.salary FROM Employees E LEFT JOIN Salary S ON E.id = S.id

zkhr
  • 739
  • 3
  • 7
-2

This should do the trick.

SELECT e.id, e.name , s.salary FROM employees e 
LEFT JOIN salary s
ON e.id=s.id
ORDER BY e.id ASC
Arda
  • 6,756
  • 3
  • 47
  • 67