Sql Query Question.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: msnews.microsoft.com (wachaca.no_at_spam.santiago.cl)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 10:44:01 -0300

Hello,
Asume I have two tables, Employess and Payroll.
Employes:
IdEmp
Fname
Lname

PayRoll
idEmp
PayDate
PayAmount

In Employess IdEmp is key, where as in PayRoll, IdEmp and PayDate are key
(you cannot get paid more than once in one day).

I need to Perform a query where I bring all the data from Employee plus the
most recent paydate and payamount.

If I perform a normal inner join I get multiple rows for all employess
because they all have been paid more than once.
I tried doing
select * from employes
inner joins (select top 1 ...) Pay on ....
But no luck.

For now what I am doing is basically a loop. Create a temporary table and
for each employee do a select on Payroll and fill the table, then do a
select * from tempTable.

Thanks for your help.