"Self-Outer" joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

"Self-Outer" joins

Author

Topic

dabeam
Starting Member

1 Post

Posted - 2009-05-26 : 18:07:56

Here is the issue: I have a table with, let's say, multiple records for each employee (e.g. a record is added each time the employee gets a raise, so it keeps track of current and previous salary amounts). I want to write a query that says: "Give me the current salary, and the most recent salary before that". We'll put a datetime stamp column so they can be put in chronological order. (This is a greatly simplified version of the real problem I'm trying to solve, but it will do.)


Create table emps (
empnum numeric,
emp_name varchar(50) )

Create table salary_hist (
empnum numeric,
salary numeric,
ChangeTime datetime)

Insert emps values (10, 'Fred')
Insert emps values (25, 'Sally')

Insert salary_hist values (10, 20000, getdate())
Insert salary_hist values (25, 25000, getdate())
Insert salary_hist values (10, 37000, getdate())

(and pretend that there was a sufficient time-delay between those three INSERT statements into salary_hist so that the three values of ChangeTime are noticeably different)

I can write a self-join to get both records for employee # 10, put into one row, thus:


Select e.emp_name, sh1.salary as Curr, sh2.salary as Before
from emps e
JOIN salary_hist sh1 on (e.empnum = sh1.empnum)
JOIN salary_hist sh2 on (sh1.empnum = sh2.empnum)
where sh1.changetime > sh2.changetime

which yields output of


Emp_Name  Curr      Before
Fred      37000     20000

The problem is that I want the SQL to include emp # 25 as well, with the Curr salary field filled in, and the Before field as NULL. I have tried it as an OUTER join:


Select e.emp_name, sh1.salary as Curr, sh2.salary as Before
from emps e
JOIN salary_hist sh1 on (e.empnum = sh1.empnum)
LEFT OUTER JOIN salary_hist sh2 on (e.empnum = sh2.empnum)
where sh1.changetime > sh2.changetime

but I still only get one row returned: the same output as before.

I have tried various combinations of ISNULL, COALESCE, etc, to try to pull out that second row, which should have an output of


Emp_Name   Curr       Before
Sally      25000      NULL

...to no avail. I think the answer lies somewhere in getting the query to understand that fields in the sh2 alias could be null due to the outer join.

Any thoughts on this would be greatly appreciated. thanks in advance.
Dave B.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 18:11:53

Select e.emp_name, sh1.salary as Curr, sh2.salary as Before
from emps e
JOIN salary_hist sh1 on (e.empnum = sh1.empnum)
LEFT OUTER JOIN salary_hist sh2 on (e.empnum = sh2.empnum)
and sh1.changetime > sh2.changetime

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources