Query help - 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 2000 Forums

Transact-SQL (2000)

Query help

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2006-11-16 : 11:05:00

The following is an example of my problem:

I have 2 tables.

TableA and TableB TableC

TableA has
Name,
EmployeeNumber
DateofBirth,
Address

TableB has
EmployeeNumber
TestDate
TestScore

TableC has
EmployeeNumber
Rank
Salary

My query is
SELECT A.NAME,A.EmployeeNUmber, A.address,b.TestScore,C.Salary,C.Rank
from TableA A
outer left join TableB B on a.EmployeeNumber=b.EmployeeNumber
outer left join TableC C on a.EmployeeNumber=c.EmployeeNumber

Howerver, when executing this query since an employee can have multiple TestScores on different TestDates, I end up with multiple records for an employee if an employee has written more than 1 testDate. If I am only interested in retrieving the latest TestDate and corresponding TestScore, what is the best way to acheive this.

Currently, I tried putting in a temp table #look

SELECT EmployeeNumber, MAX(TestDate)as TestDate
INTO #LOOK
FROM TableB
GROUP BY EmployeeNumber

Then my query changed to
SELECT A.NAME,A.EmployeeNUmber, A.address,b.TestScore,C.Salary,C.Rank
from TableA A
left outer join #look on A.EmployeeNumber = #look.EmployeeNumber
left join TableB B on B.TestDate=#look.TestDate and b.EmployeeNumber=#look.EmployeeNumber
outer left join TableC C on a.EmployeeNumber=c.EmployeeNumber

This works, just wondering if there is a more efficient way of doing this.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 11:09:23

Why temporary table? You could use derived table or better correlated subquery:

SELECT
	A.NAME,A.EmployeeNUmber, A.address,B.TestScore,C.Salary,C.Rank
from
	TableA A 
	Left Join TableC C on A.EmployeeNumber = C.EmployeeNumber
	Left Join TableB B on A.EmployeeNumber=B.EmployeeNumber
Where
	B.TestDate = 	(Select MAX(TestDate)as TestDate
			FROM TableB bb
			Where bb.EmployeeNumber = A.EmployeeNumber
			)

Harsh Athalye
India.
"Nothing is Impossible"