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

General SQL Server Forums

New to SQL Server Programming

Outer Joins

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2010-08-24 : 02:15:51

I have 2 tables, Employee and SalaryHistory.

There can only be one record per employee in the Employee table. The SalaryHistory table contains, you guessed it, the history of an employees salary. A one to many ratio between Employee and SalaryHistory. The SalaryHistory table can also have no records for a particular employee.

I am trying to write a query that will return the employee record and the most recent SalaryHistory info. If there is no SalaryHistory record, the employee data is still returned.

This works as long as a record exists in the SalaryHistory Table. If no record exists nothing is returned.

SELECT
E.FirstName,
E.LastName,
E.ID,
SH.StartingSalary,
SH.CurrentSalary
FROM
Employee E LEFT OUTER JOIN
SalaryHistory SH ON E.ID = SH.EmployeeID
WHERE (E.ID = 2) AND
SH.ID = (Select MAX(ID) from SalaryHistory where EmployeeID = 2)

Almost got it, but need a little help.

Thanks in Advance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-24 : 03:01:21

Try this:

SELECT
E.FirstName,
E.LastName,
E.ID,
SH.StartingSalary,
SH.CurrentSalary
FROM
Employee E LEFT OUTER JOIN
SalaryHistory SH ON E.ID = SH.EmployeeID
AND
SH.ID = (Select MAX(ID) from SalaryHistory sho where sho.employeeid = e.id
)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:28:02

see why it didnt return record in your case

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rypi
Yak Posting Veteran

55 Posts

Posted - 2010-08-24 : 22:24:47

Thanks Bohra and Visakh.
Got it working. Thanks for the advice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:30:45

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources